select a.log_date,a.log_filtime,b.realname,b.username,count(c.id) count from tablea a,tableb b,tablec c where a.username=b.username and a.id=c.match_id group by a.log_date,a.log_filtime,b.realname,b.username
select tablea.log_date,tablea.log_filtime,tableb.realname,tableb.username,tablec.number from tablea,tableb,tablec where tablea.username=tableb.username and tablea.id=tablec.match_id
try{ String sql="select a.log_date,a.log_filtime,b.realname,b.username,count(c.id) count from tablea a,tableb b,tablec c where a.username=b.username and a.id=c.match_id and b.dept="+部门id+" group by a.log_date,a.log_filtime,b.realname,b.username"; Statement stm = cn.Createstatement(); Resultset rs = stm.executeQuery(sql); while(rs.next()){ 记录集 } }catch(Exception ex){}
select A.log_date,A.log_filtime ,B.realname,B.username, (select count(*) from tablec where id = A.id) as 该日志的评论数 from tablea as A left join tableb as B on A.username = B.username where b.dept = 'dept_id' 这样可以实现,上面的几个不能把纪录选择全,只能选到某个user,不能把属于该部门的user选择全。
如果SQL语句写好了,就可以用JDBC实现啊!
try{
Statement st = cn.Createstatement();
Resultset rs = st.executeQuery("SQL");
}catch(Exception ex){}
if(rs.next()){
...//取出你要的结果
}
String sql="select a.log_date,a.log_filtime,b.realname,b.username,count(c.id) count from tablea a,tableb b,tablec c where a.username=b.username and a.id=c.match_id and b.dept="+部门id+" group by a.log_date,a.log_filtime,b.realname,b.username"; Statement stm = cn.Createstatement();
Resultset rs = stm.executeQuery(sql);
while(rs.next()){
记录集
}
}catch(Exception ex){}
(select count(*) from tablec where id = A.id) as 该日志的评论数
from tablea as A left join tableb as B on A.username = B.username
where b.dept = 'dept_id'
这样可以实现,上面的几个不能把纪录选择全,只能选到某个user,不能把属于该部门的user选择全。