两个表:
article文章表 字段:aid title cid
class文章类别表 字段: cid name现在表中有这些数据:
class
cid name
1 杂志
2 报纸
3 字典article
aid title cid
1 汽车杂志 1
2 瑞丽 1
3 三联生活周刊 1
4 Vista看天下 1
5 华商报 2
6 参政消息 2
7 电子游戏软件 1
8 新华字典 3
9 牛津字典 3
10 西安晚报 2
实现:
文章最多的文章类别....文章最少的文章类别SQL怎么写?
article文章表 字段:aid title cid
class文章类别表 字段: cid name现在表中有这些数据:
class
cid name
1 杂志
2 报纸
3 字典article
aid title cid
1 汽车杂志 1
2 瑞丽 1
3 三联生活周刊 1
4 Vista看天下 1
5 华商报 2
6 参政消息 2
7 电子游戏软件 1
8 新华字典 3
9 牛津字典 3
10 西安晚报 2
实现:
文章最多的文章类别....文章最少的文章类别SQL怎么写?
-- Author :SQL77(只为思齐老)
-- Date :2010-03-16 19:15:39
-- Version:
-- Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
-- Aug 6 2000 00:57:48
-- Copyright (c) 1988-2000 Microsoft Corporation
-- Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:#class
if object_id('tempdb.dbo.#class') is not null drop table #class
go
create table #class([cid] int,[name] varchar(4))
insert #class
select 1,'杂志' union all
select 2,'报纸' union all
select 3,'字典'
--> 测试数据:#article
if object_id('tempdb.dbo.#article') is not null drop table #article
go
create table #article([aid] int,[title] varchar(12),[cid] int)
insert #article
select 1,'汽车杂志',1 union all
select 2,'瑞丽',1 union all
select 3,'三联生活周刊',1 union all
select 4,'Vista看天下',1 union all
select 5,'华商报',2 union all
select 6,'参政消息',2 union all
select 7,'电子游戏软件',1 union all
select 8,'新华字典',3 union all
select 9,'牛津字典',3 union all
select 10,'西安晚报',2
--------------开始查询--------------------------select C.*,COUNT(A.CID)AS NUM
from #class C ,#article A
WHERE C.CID=A.CID
GROUP BY C.CID,C.NAME
ORDER BY 3 DESC
--select * from #article
----------------结果----------------------------
/* (所影响的行数为 3 行)
(所影响的行数为 10 行)cid name NUM
----------- ---- -----------
1 杂志 5
2 报纸 3
3 字典 2(所影响的行数为 3 行)
*/
( select count(*) from article where cid=C.CID ) from class C order by 3 desc这样写可以,效率不好
select a.*,(select count(1) from article where cid=a.cid) as cidcount from class a
(
select C.*,COUNT(A.CID)AS NUM
from #class C ,#article A
WHERE C.CID=A.CID
GROUP BY C.CID,C.NAME
)
select top 1 * from cte order by [num]
union
select top 1 * from cte order by [num] desc
-- Author :SQL77(只为思齐老)
-- Date :2010-03-16 19:15:39
-- Version:
-- Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
-- Aug 6 2000 00:57:48
-- Copyright (c) 1988-2000 Microsoft Corporation
-- Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:#class
if object_id('tempdb.dbo.#class') is not null drop table #class
go
create table #class([cid] int,[name] varchar(4))
insert #class
select 1,'杂志' union all
select 2,'报纸' union all
select 3,'字典'
--> 测试数据:#article
if object_id('tempdb.dbo.#article') is not null drop table #article
go
create table #article([aid] int,[title] varchar(12),[cid] int)
insert #article
select 1,'汽车杂志',1 union all
select 2,'瑞丽',1 union all
select 3,'三联生活周刊',1 union all
select 4,'Vista看天下',1 union all
select 5,'华商报',2 union all
select 6,'参政消息',2 union all
select 7,'电子游戏软件',1 union all
select 8,'新华字典',3 union all
select 9,'牛津字典',3 union all
select 10,'西安晚报',2
--------------开始查询--------------------------
;with f as
(
select
id=row_number()over(order by num desc),*
from
(
select C.*,COUNT(A.CID)AS NUM
from #class C ,#article A
WHERE C.CID=A.CID
GROUP BY C.CID,C.NAME
)t
)
select cid,name,num from f where id=(select min(id) from f) or id=(select max(id) from f)
----------------结果----------------------------
/*
cid name num
----------- ---- -----------
1 杂志 5
3 字典 2(2 行受影响)
*/
select class.cid,class.name,count(*) as cnt
from class inner join article on article.cid=article.cid
group by class.cid,class.name
)
order by cnt desc