这个查询怎么写？
如何求每个产品销量最高的5种，以及销量
表字段:Company,Product,sales
要求，求每个COmpany的销量最高的5个产品，及其销量
急
谢谢各位。
问题点数：50、回复次数：12Top
1 楼vivianfdlpw（）回复于 2005-11-09 16:34:12 得分 0
select * from 表 t
where sales in (select distinct top 5 sales
from 表
where company=t.company)Top
2 楼vivianfdlpw（）回复于 2005-11-09 16:34:42 得分 10
少了order by
select * from 表 t
where sales in (select distinct top 5 sales
from 表
where company=t.company
order by sales desc)
Top
3 楼GameLu（GameKing）回复于 2005-11-09 16:37:24 得分 0
结果明显不对呀
：（
Top
4 楼shou440（记得绿罗裙，处处怜芳草）回复于 2005-11-09 16:41:28 得分 0
vivianfdlpw大哥可能没看清要求：
求每个Company的销量最高的5个Product
可以用游标实现。Top
5 楼GameLu（GameKing）回复于 2005-11-09 16:43:36 得分 0
SELECT *
FROM SrvCount t
WHERE (sales IN
(SELECT DISTINCT TOP 5 sales
FROM srvcount
WHERE company = t.company
ORDER BY sales DESC))
-----------------------------------------------------------
sales Company Product
1 1195 AAAQ
2 1195 AAAR
599 1195 HHTCA
1 1195 DDYCJT
1 1195 HHWRZQ
2 1195 HHXXBB
90 1203 JXT
5 1203 DXCLUB
20 1203 MLCLUB
23 1203 JDXYCLUB
1 1211 SB
5502 1211 TQ
12 1211 UA
1 1211 WT
71 1212 XXCHBTop
6 楼GameLu（GameKing）回复于 2005-11-09 16:44:08 得分 0
1195 Company 有6条记录丫
Top
7 楼wangdehao（找找找(现在很幸福)）回复于 2005-11-09 17:05:11 得分 0
select * from (select Company,Product,sum(sales)as sales from [table] group by Company,Product)a
where
(
(
select count(*) from
(select Company,Product,sum(sales)as sales from [table] group by Company,Product)b
where a.Company = b.Company and b.sales>a.sales
))<=4Top
8 楼zlp321002（Life Is Good,Let's Shine）回复于 2005-11-09 17:14:11 得分 40
--try
select * from 表 A
where Product in
(select top 5 Product from 表 where Company=A.Company order by sales desc)
order by CompanyTop
9 楼GameLu（GameKing）回复于 2005-11-09 17:17:23 得分 0
汗
我用游标做的，还是结果不对
DECLARE @CompanyName varchar(11), @Product varchar(20), @Counts varchar(40),
@message varchar(80)
DECLARE Company_cursor CURSOR FOR
SELECT distinct Company
FROM SrvCount
ORDER BY Company
OPEN Company_cursor
FETCH NEXT FROM Company_cursor
INTO @CompanyName
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @message
DECLARE Product_cursor CURSOR FOR
SELECT Top 5 Products,Sales
FROM SrvCount
WHERE Company = @CompanyName
ORDER BY Sales Desc
OPEN Product_cursor
FETCH NEXT FROM Product_cursor INTO @Product,@Counts
IF @@FETCH_STATUS <> 0
PRINT ' <<No Items>>'
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @message = @CompanyName + '|' + @Product + '|' + @Counts
PRINT @message
FETCH NEXT FROM Product_cursor INTO @Product,@Counts
END
CLOSE Product_cursor
DEALLOCATE Product_cursor
-- Get the next author.
FETCH NEXT FROM Company_cursor
INTO @CompanyName
END
CLOSE Company_cursor
DEALLOCATE Company_cursor
-------------------------------------------------
1195|HHTCA|599
1195|AAAR|2
1195|HHXXBB|2
1195|HHWRZQ|1
1195|DDYCJT|1
1195|DDYCJT|1
1203|JXT|90
1203|JDXYCLUB|23
1203|MLCLUB|20
1203|DXCLUB|5
1203|DXCLUB|5
1211|TQ|5502
1211|UA|12
1211|WT|1
1211|SB|1
1211|SB|1
1212|XXSNRJ|727
1212|SHRJZN|307
1212|YXBYA|97
1212|XXCHB|71
1212|YXZXCS|12
1212|YXZXCS|12Top
10 楼vivianfdlpw（）回复于 2005-11-09 17:19:56 得分 0
1195 Company 有6条记录丫
=========>
因为存在销量并列的的情况Top
11 楼GameLu（GameKing）回复于 2005-11-09 18:14:34 得分 0
可是我是 SELECT Top 5 Products,Sales
FROM SrvCount
Top 5 出来的
Top
12 楼GameLu（GameKing）回复于 2005-11-09 18:15:06 得分 0
zlp321002 的结果正确
结帖
谢谢各位
Top
