select top 10 主题 from (select 主题,count(评论) as totalCount from A,B where A.主题ID = B.评论ID group by 主题) order by totalCount
select S.* from A as S inner join (select top 10 主题ID,Count(ID) as amount from B group by 主题ID order by amount desc) as T on T.主题ID = S.ID
select top 10 * from (select A.name from A ,B where A.name = B.name)
/*创建测试用表,不加主键或外键了*/ create table 主题表(主题ID INT not null,主题名称 nvarchar(200)) go create table 评论表(评论ID int not null,主题ID int not null,评论名称 NVARCHAR(200)) GO /*导入测试数据*/ insert into 主题表 select 1,'a' union all select 2,'b' union all select 3,'c' union all select 4,'d' union all select 5,'e' union all select 6,'f' union all select 7,'g' union all select 8,'h' union all select 9,'i' union all select 10,'j' union all select 11,'k' union all select 12,'l'goinsert into 评论表 select 1,1,'aa' union all select 2,1,'ab' union all select 3,1,'ab' union all select 4,2,'ab' union all select 5,2,'ab' union all select 6,2,'ab' union all select 7,2,'ab' union all select 8,3,'ab' union all select 9,4,'ab' union all select 10,4,'ab' union all select 11,5,'ab' union all select 12,5,'ab' union all select 13,6,'ab' union all select 14,7,'ab' union all select 15,7,'ab' union all select 16,7,'ab' union all select 17,7,'ab' union all select 18,8,'ab' union all select 19,8,'ab' union all select 20,8,'ab' union all select 21,8,'ab' union all select 22,8,'ab' union all select 23,9,'ab' union all select 24,9,'ab' union all select 25,10,'ab' union all select 26,11,'ab' union all select 27,11,'ab' union all select 28,11,'ab' union all select 29,12,'ab' union all select 30,12,'ab' union all select 31,12,'ab' union all select 32,10,'ab' union all select 33,11,'ab' union all select 34,8,'ab' union all select 35,4,'ab' union all select 36,8,'ab' union all select 37,9,'ab' union all select 38,10,'ab' union all select 39,11,'ab' union all select 40,12,'ab' union all select 41,10,'ab' union all select 42,1,'ab' union all select 43,2,'ab' union all select 44,3,'ab' union all select 45,5,'ab' union all select 46,5,'ab' union all select 47,9,'ab' union all select 48,8,'ab' union all select 49,7,'ab' union all select 50,7,'ab' union all select 51,7,'ab' union all select 52,6,'ab' union all select 53,3,'ab' go
/*sql语句*/ select top 10 b.主题ID,a.主题名称,count(b.主题ID) as cnt from 评论表 b,主题表 a where a.主题ID=b.主题ID group by b.主题ID,a.主题名称 order by count(b.主题ID) desc/*结果*/ --------------------------------- 主题ID 主题名称 cnt 8 h 8 7 g 7 2 b 5 11 k 5 10 j 4 9 i 4 1 a 4 5 e 4 12 l 4 4 d 3 ---------------------------------/*清除测试表*/ drop table 主题表 godrop table 评论表 go
稍微完善一下: select top 10 b.主题ID,a.主题名称,count(b.主题ID) as 评论条数 from 评论表 b,主题表 a where a.主题ID=b.主题ID group by b.主题ID,a.主题名称 order by 评论条数 desc
select top 10 主题 from (select 主题,count(评论) as totalCount from A,B where A.主题ID = B.评论ID group by 主题) order by totalCount
inner join (select top 10 主题ID,Count(ID) as amount from B group by 主题ID order by amount desc) as T on T.主题ID = S.ID
create table 主题表(主题ID INT not null,主题名称 nvarchar(200))
go
create table 评论表(评论ID int not null,主题ID int not null,评论名称 NVARCHAR(200))
GO
/*导入测试数据*/
insert into 主题表
select 1,'a'
union all
select 2,'b'
union all
select 3,'c'
union all
select 4,'d'
union all
select 5,'e'
union all
select 6,'f'
union all
select 7,'g'
union all
select 8,'h'
union all
select 9,'i'
union all
select 10,'j'
union all
select 11,'k'
union all
select 12,'l'goinsert into 评论表
select 1,1,'aa'
union all
select 2,1,'ab'
union all
select 3,1,'ab'
union all
select 4,2,'ab'
union all
select 5,2,'ab'
union all
select 6,2,'ab'
union all
select 7,2,'ab'
union all
select 8,3,'ab'
union all
select 9,4,'ab'
union all
select 10,4,'ab'
union all
select 11,5,'ab'
union all
select 12,5,'ab'
union all
select 13,6,'ab'
union all
select 14,7,'ab'
union all
select 15,7,'ab'
union all
select 16,7,'ab'
union all
select 17,7,'ab'
union all
select 18,8,'ab'
union all
select 19,8,'ab'
union all
select 20,8,'ab'
union all
select 21,8,'ab'
union all
select 22,8,'ab'
union all
select 23,9,'ab'
union all
select 24,9,'ab'
union all
select 25,10,'ab'
union all
select 26,11,'ab'
union all
select 27,11,'ab'
union all
select 28,11,'ab'
union all
select 29,12,'ab'
union all
select 30,12,'ab'
union all
select 31,12,'ab'
union all
select 32,10,'ab'
union all
select 33,11,'ab'
union all
select 34,8,'ab'
union all
select 35,4,'ab'
union all
select 36,8,'ab'
union all
select 37,9,'ab'
union all
select 38,10,'ab'
union all
select 39,11,'ab'
union all
select 40,12,'ab'
union all
select 41,10,'ab'
union all
select 42,1,'ab'
union all
select 43,2,'ab'
union all
select 44,3,'ab'
union all
select 45,5,'ab'
union all
select 46,5,'ab'
union all
select 47,9,'ab'
union all
select 48,8,'ab'
union all
select 49,7,'ab'
union all
select 50,7,'ab'
union all
select 51,7,'ab'
union all
select 52,6,'ab'
union all
select 53,3,'ab'
go
select top 10 b.主题ID,a.主题名称,count(b.主题ID) as cnt from 评论表 b,主题表 a where a.主题ID=b.主题ID group by b.主题ID,a.主题名称 order by count(b.主题ID) desc/*结果*/
---------------------------------
主题ID 主题名称 cnt
8 h 8
7 g 7
2 b 5
11 k 5
10 j 4
9 i 4
1 a 4
5 e 4
12 l 4
4 d 3
---------------------------------/*清除测试表*/
drop table 主题表
godrop table 评论表
go
select top 10 b.主题ID,a.主题名称,count(b.主题ID) as 评论条数 from 评论表 b,主题表 a where a.主题ID=b.主题ID group by b.主题ID,a.主题名称 order by 评论条数 desc