是一个论坛的查询版块表(plate)
列:
id
name
oid(oid为主版块,oid相同的版块处于同一主版块下)帖子表:
id
tit
lastmodifyid(用户表外键)
lastmodifytime
plateid(版块表外键)用户表
id
username
我要根据主版块(plate.oid)分组查询该主版块下的信息
查询结果为:plate.name sum(小版块下的帖子总数) tit(该小版块最后修改的帖子标题) lastmodifyuser(最后修改的用户)
列:
id
name
oid(oid为主版块,oid相同的版块处于同一主版块下)帖子表:
id
tit
lastmodifyid(用户表外键)
lastmodifytime
plateid(版块表外键)用户表
id
username
我要根据主版块(plate.oid)分组查询该主版块下的信息
查询结果为:plate.name sum(小版块下的帖子总数) tit(该小版块最后修改的帖子标题) lastmodifyuser(最后修改的用户)
(select tit from 帖子表 where plateid=a.plateid and lastmodifytime=(select max(lastmodifytime) from 帖子表 where plateid=a.plateid))as tit,
(select t2.username from 用户表 t2 inner join 帖子表 t1 on t1.lastmodifyid=t2.id where t1.plateid=a.plateid and t1.lastmodifytime=(select max(lastmodifytime) from 帖子表 where plateid=a.plateid))as lastmodifyusername
from plate a inner join 帖子表 b on a.id=b.plateid
(select tit from 帖子表 where plateid=a.plateid and lastmodifytime=(select max(lastmodifytime) from 帖子表 where plateid=a.plateid))as tit,
(select t2.username from 用户表 t2 inner join 帖子表 t1 on t1.lastmodifyid=t2.id where t1.plateid=a.plateid and t1.lastmodifytime=(select max(lastmodifytime) from 帖子表 where plateid=a.plateid))as lastmodifyusername
from plate a inner join 帖子表 b on a.id=b.plateid
group by a.name
[小版块下的帖子总数] = (select count(1) from 帖子表 n where n.plateid = m.id),
[该小版块最后修改的帖子标题] = (select top 1 tit from 帖子表 n where n.plateid = m.id order by n.lastmodifytime desc),
[最后修改的用户] = (select top 1 t.username from 帖子表 n , 用户表 t where n.plateid = m.id and n.lastmodifyid = t.id order by n.lastmodifytime desc)
from 版块表 m