如何统计出不同表中的数据,并且显示出结果
我要查询的东西:
" <table width=\ "100%\ " border=\ "0\ " cellspacing=\ "0\ " cellpadding=\ "0\ "> \r\n ");
out.println( " <tr> \r\n ");
out.println( " <td width=\ "50\ " align=\ "center\ "> ID </td> \r\n ");
out.println( " <td width=\ "100\ " align=\ "center\ "> 用户名 </td> \r\n ");
out.println( " <td width=\ "100\ " align=\ "center\ "> 姓名 </td> \r\n ");
out.println( " \r\n ");
out.println( " <td width=\ "80\ " align=\ "center\ "> 接收信息数 </td> \r\n ");
out.println( " <td width=\ "80\ " align=\ "center\ "> 发送信息数 </td> \r\n ");
out.println( " </tr> \r\n ");
int i=0;
while(rs.next())
{
out.println( " <tr> \r\n ");
out.println( " <td align=\ "center\ "> "+(++i)+ " </td> \r\n ");
out.println( " <td align=\ "center\ "> "+rs.getString( "username ")+ " </td> \r\n ");
out.println( " <td align=\ "center\ "> "+rs.getString( "zsname ")+ " </td> \r\n ");
//out.println( " <td align=\ "center\ "> "+rs.getString( "innum ")+ " </td> \r\n ");
//out.println( " <td align=\ "center\ "> "+rs.getString( "outnum ")+ " </td> \r\n ");
out.println( " <td> </td> \r\n ");
out.println( " <td> </td> \r\n ");
out.println( " <td> </td> \r\n ");
out.println( " </tr> \r\n ");
}
查询统计语句:
String sql= "select * from userinfo ";
//接受信息统计
String insql= "SELECT Count(*) as innum FROM listinfo where messagesender= ' "+username+ " ' ";
//发送信息统计
String outsql= "SELECT Count(*) as outnum FROM listinfo where messageincept= ' "+username+ " ' ";
rs=conn.executeQuery(sql);
rs=conn.executeQuery(insql);
rs=conn.executeQuery(outsql);
这样写不对,因为用户发送信息数是根据这个循环查询来的.
要怎么解决?
[解决办法]
多个语句合起来，一个分组语句搞定
[解决办法]
group by
[解决办法]
SELECT username,Count(*) as innum FROM listinfo where messagesender= ' "+username+ " ' group by username
[解决办法]
sql = "SELECT username,Count(*) as innum FROM listinfo where messagesender= ' "+username+ " ' group by username ";
rs = conn.executeQuery(sql);
