求一Sql 语句

[田老狮]

表结构如下

产品表：
Create Table Product
(
ID int, //记录ID
ProName nvarchar(50), //产品名称
ProBigClass int, //产品大类，
ProSmallCalss int //产品小类
..........
)

产品大类和小类信息有专门表进行存储，分别为ProductBigClass,ProductSmallClass

产品大类：
Create Table ProductBigClass
(
ID int,
ClassName nvarchar(50)
)

产品小类：
Create Table ProductSmallClass
(
ID int,
ClassName nvarchar(50),
BigClassID int //所属大类
)

现在的问题是：找出每个大类里面产品数最多的4个小类,请大家帮忙看一下....

[lxzm1001]

感觉没有说的清楚？？

[云中客]

楼主的问题没有表达清楚,你的小类里的产品数量都没有,怎么来统计最多的4个小类

[dulei115]

if object_id('tempdb..#') is not null drop table #
select ProBigClass, ProSmallClass, count(1) as ProCount
into #
from Product

select *
from # a
where (select count(1)
from #
where ProBigClass = a.ProBigClass and ProCount > a.ProCount) < 4

drop table #

[dulei115]

if object_id('tempdb..#') is not null drop table #
select ProBigClass, ProSmallClass, count(1) as ProCount
into #
from Product

select b.ClassName as ProBigClassName, c.ClassName as ProSmallClassName, a.ProCount
from # a join ProductBigClass b on a.ProBigClass = b.ID
join ProductSmallClass c on a.ProSmallClass = c.ID
where (select count(1)
from #
where ProBigClass = a.ProBigClass and ProCount > a.ProCount) < 4

drop table #

[冷箫轻笛]

--建表
Create Table Product
(
ID int, --//记录ID
ProName nvarchar(50), --//产品名称
ProBigClass int, --//产品大类，
ProSmallCalss int --//产品小类
)

insert into product select 1,'1a',1,11
insert into product select 2,'1b',1,12
insert into product select 3,'1c',1,12
insert into product select 4,'1d',1,13
insert into product select 5,'1e',1,13
insert into product select 11,'1f',1,13
insert into product select 12,'1g',1,14
insert into product select 13,'1h',1,14
insert into product select 14,'1i',1,14
insert into product select 15,'1j',1,14
insert into product select 21,'1k',1,15
insert into product select 22,'1l',1,15
insert into product select 23,'1m',1,15
insert into product select 24,'1n',1,15
insert into product select 25,'1o',1,15

insert into product select 31,'2a',2,21
insert into product select 32,'2b',2,22
insert into product select 33,'2c',2,22
insert into product select 34,'2d',2,23
insert into product select 35,'2e',2,23
insert into product select 36,'2f',2,23
insert into product select 37,'2g',2,24
insert into product select 38,'2h',2,24
insert into product select 39,'2i',2,24
insert into product select 40,'2j',2,24
insert into product select 41,'2k',2,25
insert into product select 42,'2l',2,25
insert into product select 43,'2m',2,25
insert into product select 44,'2n',2,25
insert into product select 45,'2o',2,25


--查询
select *
from
(select ProBigClass,ProSmallCalss,count(1) as 数量
from product
group by ProBigClass,ProSmallCalss
)t1
where ProBigClass+ProSmallCalss+cast(数量 as varchar) IN
(select top 4 ProBigClass+ProSmallCalss+cast(数量 as varchar) from
(select ProBigClass,ProSmallCalss,count(1) as 数量
from product
group by ProBigClass,ProSmallCalss) t2
where ProBigClass = t1.ProBigClass order by 数量 desc)

--结果
1 12 2
1 13 3
1 14 4
1 15 5
2 22 2
2 23 3
2 24 4
2 25 5

[田老狮]

就是说，现在在产品表中有很多产品，分别属于不同的大类，而每个大类又分了很多小类

现在就是要找出每个大类里产品最多的4个小类的ID

[liangpei2008]

给出ProductSmallClass表的数据！