帖子表Topic:一行: TID int identity(1,1) 自增编号. 回复表Replay: 第一行:RID int identity(1,1) 自增编号. 第二行:TID int 帖子编号(和帖子表有主外键关系) 第三行:CreateTime DateTime 创建时间
SELECT TOP(10) tid, cnt=COUNT(1) FROM Topic a INNER JOIN Replay b ON a.tid = b.tid WHERE b.CreateTime >= DATEADD(DAY, -1, GETDATE()) --24小时即1天 GROUP BY tid ORDER BY cnt DESC
SORRY啊,没测试。是有问题,请参考: SELECT TOP(10) a.tid, cnt=COUNT(1) FROM Topic a INNER JOIN Replay b ON a.tid = b.tid WHERE b.CreateTime >= DATEADD(DAY, -1, GETDATE()) --24小时即1天 GROUP BY a.tid ORDER BY cnt DESC
select top 10 tid ,cnt=COUNT(*) from TOPIC "a" inner join Replay "b" on a.tid=b.tid WHERE b.CreateTime >= DATEADD(DAY, -1, GETDATE()) --24小时即1天 group by a.tid order by cnt desc;
select top 10 tid ,cnt=COUNT(*) from TOPIC "a" inner join Replay "b" on a.tid=b.tid WHERE b.CreateTime >= DATEADD(DAY, -1, GETDATE()) --24小时即1天 group by a.tid order by cnt desc;
SORRY啊,没测试。是有问题,请参考: SELECT TOP(10) a.tid, cnt=COUNT(1) FROM Topic a INNER JOIN Replay b ON a.tid = b.tid WHERE b.CreateTime >= DATEADD(DAY, -1, GETDATE()) --24小时即1天 GROUP BY a.tid ORDER BY cnt DESC这样就对了
帖子表Topic:一行: TID int identity(1,1) 自增编号.
回复表Replay:
第一行:RID int identity(1,1) 自增编号.
第二行:TID int 帖子编号(和帖子表有主外键关系)
第三行:CreateTime DateTime 创建时间
FROM Topic a
INNER JOIN Replay b
ON a.tid = b.tid
WHERE b.CreateTime >= DATEADD(DAY, -1, GETDATE()) --24小时即1天
GROUP BY tid
ORDER BY cnt DESC
--DATEADD(DAY, -1, GETDATE()),DATEADD是一个日期函数,上网查一下便知,它的意思是:当前时间(GETDATE())减去1天,得到的一个时间值。测下便知
SELECT TOP(10) a.tid, cnt=COUNT(1)
FROM Topic a
INNER JOIN Replay b
ON a.tid = b.tid
WHERE b.CreateTime >= DATEADD(DAY, -1, GETDATE()) --24小时即1天
GROUP BY a.tid
ORDER BY cnt DESC
from TOPIC "a"
inner join Replay "b"
on a.tid=b.tid
WHERE b.CreateTime >= DATEADD(DAY, -1, GETDATE()) --24小时即1天
group by a.tid
order by cnt desc;
from TOPIC "a"
inner join Replay "b"
on a.tid=b.tid
WHERE b.CreateTime >= DATEADD(DAY, -1, GETDATE()) --24小时即1天
group by a.tid
order by cnt desc;
SELECT TOP(10) a.tid, cnt=COUNT(1)
FROM Topic a
INNER JOIN Replay b
ON a.tid = b.tid
WHERE b.CreateTime >= DATEADD(DAY, -1, GETDATE()) --24小时即1天
GROUP BY a.tid
ORDER BY cnt DESC这样就对了