表a是主表,表b是对表a进行评论的内容,好比是论坛对某条进行评论,
所以表a里的一条数据对应表b里几条数据
现在想显示的结果是表a全部显示出来,然后表b里显示时间最新的一条
表a
rid rname
1 ljf
2 jkm
3 lop
--------------------
表b
bid rid bname btime
1 1 xxj 2006-1-18
2 1 wm 2006-1-19
3 2 mzd 2006-1-20
4 2 zel 2006-1-21
这里表a第3条数据在表b里没有对应的数据,
那么表a里这条数据也要显示出来
select b.rid,max(btime) as bbtime,a.rid,a.rname from b
left join a on a.rid=b.rid
group by b.rid,a.rid,a.rname
如果表b是这样的话,你看得到的结果是什么?
我这里结果是
1 ljf 2006-1-19
2 jkm 2006-1-21
应该是
select b.rid,max(btime) as bbtime,a.rid,a.rname from a
left join b on a.rid=b.rid
group by b.rid,a.rid,a.rname
这样的结果是(正确,也就是这里表a第3条数据在表b里没有对应的数据,那么表a里这条数据也要显示出来):
1 ljf 2006-1-19
2 jkm 2006-1-21
3 lop
----------------------------------------------------
先 where 然后 group by 最后 order by id desc
格式:select table from xxx where yyy group by zzz order by id desc
---------------------------------------
例子:
string sql="select lw_guest.guest_name,max(guest_date) as guest_date,lw_repday.repday_name,lw_repday.departmentname,lw_repday.repday_indate,lw_repday.repday_editdate,lw_repday.repday_id from lw_repday left join lw_guest on lw_repday.repday_id=lw_guest.repday_id where lw_repday.company='"+Session["company"]+"' and lw_repday.repday_name in (select name from lw_popedom where users_id='"+Session["users_id"]+"') ";