主键评论信息id 每条新闻的id 评论的标题 评论的内容
com_id key_id com_title com_content
1 3 title1 content1
2 3 title2 content2
3 3 title3 content3
4 7 title4 content4
5 7 title5 content5
6 9 title6 content6
7 9 title7 content7
8 9 title8 content8
9 9 title9 content9
10 8 title10 content10
11 8 title11 content11
12 8 title12 content12
13 8 title13 content13
14 8 title14 content14
15 8 title15 content15这里有15条评论信息,记录了对key_id为3,7,9,8新闻的评论信息,现在我要求前3条评论信息最多的新闻key_id,查出来的结果应该是:key_id
8 //它有6条评论
9 //它有4条评论
3 //它有3条评论
com_id key_id com_title com_content
1 3 title1 content1
2 3 title2 content2
3 3 title3 content3
4 7 title4 content4
5 7 title5 content5
6 9 title6 content6
7 9 title7 content7
8 9 title8 content8
9 9 title9 content9
10 8 title10 content10
11 8 title11 content11
12 8 title12 content12
13 8 title13 content13
14 8 title14 content14
15 8 title15 content15这里有15条评论信息,记录了对key_id为3,7,9,8新闻的评论信息,现在我要求前3条评论信息最多的新闻key_id,查出来的结果应该是:key_id
8 //它有6条评论
9 //它有4条评论
3 //它有3条评论
from tb
group by key_id
order by cnt
from tb
group by key_id
order by cnt
/*---------------------------------
-- Author : htl258(Tony)
-- Date : 2009-09-15 22:16:25
-- Version: Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.1 (Build 2600: Service Pack 3)---------------------------------*/
--> 生成测试数据表:tbIF NOT OBJECT_ID('[tb]') IS NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([com_id] INT,[key_id] INT,[com_title] NVARCHAR(10),[com_content] NVARCHAR(10))
INSERT [tb]
SELECT 1,3,'title1','content1' UNION ALL
SELECT 2,3,'title2','content2' UNION ALL
SELECT 3,3,'title3','content3' UNION ALL
SELECT 4,7,'title4','content4' UNION ALL
SELECT 5,7,'title5','content5' UNION ALL
SELECT 6,9,'title6','content6' UNION ALL
SELECT 7,9,'title7','content7' UNION ALL
SELECT 8,9,'title8','content8' UNION ALL
SELECT 9,9,'title9','content9' UNION ALL
SELECT 10,8,'title10','content10' UNION ALL
SELECT 11,8,'title11','content11' UNION ALL
SELECT 12,8,'title12','content12' UNION ALL
SELECT 13,8,'title13','content13' UNION ALL
SELECT 14,8,'title14','content14' UNION ALL
SELECT 15,8,'title15','content15'
GO
--SELECT * FROM [tb]-->SQL查询如下:
select top 3 key_id from tb group by key_id order by count(1) desc
/*
key_id
-----------
8
9
3(所影响的行数为 3 行)
*/
(select count(key_id),key_id from tb group by key_id)t
select 1,3,'title1','content1' union all
select 2,3,'title2','content2' union all
select 3,3,'title3','content3' union all
select 4,7,'title4','content4' union all
select 5,7,'title5','content5' union all
select 6,9,'title6','content6' union all
select 7,9,'title7','content7' union all
select 8,9,'title8','content8' union all
select 9,9,'title9','content9' union all
select 10,8,'title10','content10' union all
select 11,8,'title11','content11' union all
select 12,8,'title12','content12' union all
select 13,8,'title13','content13' union all
select 14,8,'title14',' content14' union all
select 15,8,'title15','content15'
--这里有15条评论信息,记录了对key_id为3,7,9,8新闻的评论信息,现在我要求前3条评论信息最多的新闻key_id,查出来的结果应该是:select top 3 key_id,count(*) num from tb group by key_id
order by num desc
from tb a
group by a.key_id
order by count(1) desc
/*
key_id 评论数
8 6
9 4
3 3
*/