select top 10 * from (select id,count(1) as 发帖次数 from 帖子表 group by id)a, (select id,count(1) as 回复次数 from 回复表 group by id)b where a.id=b.id order by b.回复次数 desc
我在加一点东西
两张表的样子 你看一下
帖子表 回复表 Tid RId Tcontent Tid(外键) TName CreatTime
select top 10 a.tname from 帖子表 a,(select tid,count(1) as 回复次数 from 回复表 group by tid)b where a.tid=b.tid and datediff(hh,CreatTime,getdate())=24 order by b.回复次数 desc
select top 10 m.* , count(1) 回复次数 from 帖子表 m , 回复表 n where m.Tid = n.Tid and datediff(hh,n.CreatTime,getdate()) <= 24 order by 回复次数 descselect top 10 m.* , count(1) 回复次数 from 帖子表 m , 回复表 n where m.Tid = n.Tid and datediff(dd,n.CreatTime,getdate()) <= 1 order by 回复次数 desc
select top 10 * from (select id,count(1) as 发帖次数 from 帖子表 where datediff(hh,createtime,getdate())<24 group by id)a, (select id,count(1) as 回复次数 from 回复表 where datediff(hh,createtime,getdate())<24 group by id)b where a.id=b.id order by b.回复次数 desc
select top 10 a.tname from 帖子表 a,(select tid,count(1) as 回复次数 from 回复表 group by tid)b where a.tid=b.tid and datediff(hh,CreatTime,getdate())<=24 order by b.回复次数 desc
我也遇到这个问题,这是我参考楼上各位大侠写的,不知道有错没……SELECT TOP 10 T.*, COUNT(R.Rid) AS '回复数' FROM 帖子表 AS 'T' INNER JOIN 回复表 AS 'R' ON T.Tid = R.Tid WHERE DATEDIFF(dd, R.CreateTime, GETDATE()) <= 1 GROUP BY R.Tid ORDER BY R.回复数 DESC
还是这样更好一点呢?望大虾们多多指教啊。。SELECT TOP 10 T.Tid, NR.回复数 FROM 帖子表 AS 'T' RIGHT OUTER JOIN ( SELECT Tid, CreateTime, COUNT(1) AS '回复数' FROM 回复表 GROUP BY Tid, CreatTime ) AS 'NR' ON T.Tid = NR.Tid WHERE NR.CreateTime >= DATEADD(dd, -1, GETDATE()) ORDER BY NR.回复数 DESC
top 10 *
from
(select id,count(1) as 发帖次数 from 帖子表 group by id)a,
(select id,count(1) as 回复次数 from 回复表 group by id)b
where
a.id=b.id
order by
b.回复次数 desc
两张表的样子 你看一下
帖子表 回复表
Tid RId
Tcontent Tid(外键)
TName CreatTime
top 10 a.tname
from
帖子表 a,(select tid,count(1) as 回复次数 from 回复表 group by tid)b
where
a.tid=b.tid
and
datediff(hh,CreatTime,getdate())=24
order by
b.回复次数 desc
select
top 10 *
from
(select id,count(1) as 发帖次数 from 帖子表 where datediff(hh,createtime,getdate())<24 group by id)a,
(select id,count(1) as 回复次数 from 回复表 where datediff(hh,createtime,getdate())<24 group by id)b
where
a.id=b.id
order by
b.回复次数 desc
我觉得在应在是主表的createtime与最后回复的时间小于24 记录最多的。
select
top 10 a.tname
from
帖子表 a,(select tid,count(1) as 回复次数 from 回复表 group by tid)b
where
a.tid=b.tid
and
datediff(hh,CreatTime,getdate())<=24
order by
b.回复次数 desc
FROM 帖子表 AS 'T'
INNER JOIN 回复表 AS 'R'
ON T.Tid = R.Tid
WHERE DATEDIFF(dd, R.CreateTime, GETDATE()) <= 1
GROUP BY R.Tid
ORDER BY R.回复数 DESC
FROM 帖子表 AS 'T'
RIGHT OUTER JOIN
(
SELECT Tid, CreateTime, COUNT(1) AS '回复数'
FROM 回复表 GROUP BY Tid, CreatTime
) AS 'NR'
ON T.Tid = NR.Tid
WHERE NR.CreateTime >= DATEADD(dd, -1, GETDATE())
ORDER BY NR.回复数 DESC