第一个问题没看明白第二个问题 Select a.id,a.parentId , a.sendtime From tbComp AS a Where Exists (Select sendtime From tbComp Where a.sendtime >= tbComp.sendtime) Group By a.id,a.parentId,a.sendtime Having a.id<>0 and a.parentId =0
Select id,parentId , sendtime From (select top 1 sendtime from tbComp ) a Group a.sendtime Having a.id<>0 and a.parentId =0
表tbCompID号 父ID 发布时间
id || parentId(0为根ID) || sendtime1 0 2005-12-12
2 0 2005-12-12
3 2 2005-12-13
4 1 2005-12-14
5 2 2005-12-13
.............按父ID最多的排序
结果
2 0 2005-12-12
1 0 2005-12-12另一个是求最后回复时间的所有父ID为0的数据,SELECT id FROM tbComp ...... ORDER BY sendTime DESC,ID不为0的数据不包含进来.结果
1 0 2005-12-12
2 0 2005-12-12
id || parentId(0为根ID) || sendtime1 0 2005-12-12
2 0 2005-12-12
3 2 2005-12-13
4 1 2005-12-14
5 2 2005-12-13
.............按父ID最多的排序
结果
2 0 2005-12-12
1 0 2005-12-12假设这是一个留言表parentId为0代表是贴子,不为0代表是ID号为X的回贴,按回贴数最多的贴子排序和最后回复时间排序:》
Select a.id,a.parentId , a.sendtime From tbComp AS a
Where Exists (Select sendtime From tbComp Where a.sendtime >= tbComp.sendtime)
Group By a.id,a.parentId,a.sendtime
Having a.id<>0 and a.parentId =0
From (select top 1 sendtime from tbComp ) a
Group a.sendtime
Having a.id<>0 and a.parentId =0