在SQL Server 数据库有张表如下:类别 品名 金额
A PM1 ¥12
A PM2 ¥25
A PM3 ¥4
A PM4 ¥8
A PM5 ¥8
A PM6 ¥85
A PM7 ¥656
A PM8 ¥5
A PM9 ¥5
B PM10 ¥8
B PM11 ¥5
B PM12 ¥5
B PM13 ¥52
B PM14 ¥74
B PM1 ¥4
B PM2 ¥54
B PM3 ¥454
B PM4 ¥5
B PM5 ¥5,445
C PM6 ¥5
C PM7 ¥515
C PM22 ¥8
C PM21 ¥8
C PM24 ¥4
C PM12 ¥4,744
C PM26 ¥44
C PM27 ¥521
C PM28 ¥48
想通过查询得到如下:类别 品名 金额
A PM7 ¥656
A PM6 ¥85
A PM2 ¥25
A PM1 ¥12
B PM5 ¥5,445
B PM3 ¥454
B PM14 ¥74
B PM13 ¥52
C PM12 ¥4,744
C PM27 ¥521
C PM7 ¥515
C PM28 ¥48
也就是每个类别的金额排名前4位。类别动态的,有时查询一个类别,有时查询多个类别。 请各位帮个忙,这语句应该如何写。
A PM1 ¥12
A PM2 ¥25
A PM3 ¥4
A PM4 ¥8
A PM5 ¥8
A PM6 ¥85
A PM7 ¥656
A PM8 ¥5
A PM9 ¥5
B PM10 ¥8
B PM11 ¥5
B PM12 ¥5
B PM13 ¥52
B PM14 ¥74
B PM1 ¥4
B PM2 ¥54
B PM3 ¥454
B PM4 ¥5
B PM5 ¥5,445
C PM6 ¥5
C PM7 ¥515
C PM22 ¥8
C PM21 ¥8
C PM24 ¥4
C PM12 ¥4,744
C PM26 ¥44
C PM27 ¥521
C PM28 ¥48
想通过查询得到如下:类别 品名 金额
A PM7 ¥656
A PM6 ¥85
A PM2 ¥25
A PM1 ¥12
B PM5 ¥5,445
B PM3 ¥454
B PM14 ¥74
B PM13 ¥52
C PM12 ¥4,744
C PM27 ¥521
C PM7 ¥515
C PM28 ¥48
也就是每个类别的金额排名前4位。类别动态的,有时查询一个类别,有时查询多个类别。 请各位帮个忙,这语句应该如何写。
-- Author: happyflystone
-- Version:V1.001
-- Date:2008-11-16 12:02:43
-------------------------------------- Test Data: tb
If object_id('tb') is not null
Drop table tb
Go
Create table tb(类别 nvarchar(1),品名 nvarchar(4),金额 int)
Go
Insert into tb
select 'A','PM1',12 union all
select 'A','PM2',25 union all
select 'A','PM3',4 union all
select 'A','PM4',8 union all
select 'A','PM5',8 union all
select 'A','PM6',85 union all
select 'A','PM7',656 union all
select 'A','PM8',5 union all
select 'A','PM9',5 union all
select 'B','PM10',8 union all
select 'B','PM11',5 union all
select 'B','PM12',5 union all
select 'B','PM13',52 union all
select 'B','PM14',74 union all
select 'B','PM1',4 union all
select 'B','PM2',54 union all
select 'B','PM3',454 union all
select 'B','PM4',5 union all
select 'B','PM5',5445 union all
select 'C','PM6',5 union all
select 'C','PM7',515 union all
select 'C','PM22',8 union all
select 'C','PM21',8 union all
select 'C','PM24',4 union all
select 'C','PM12',4744 union all
select 'C','PM26',44 union all
select 'C','PM27',521 union all
select 'C','PM28',48
Go
--Start
Select *
from tb a
where (select count(1) from tb where a.类别 = 类别 and 金额 > a.金额) < 4
order by 1,3 desc--Result:
/*
类别 品名 金额
---- ---- -----------
A PM7 656
A PM6 85
A PM2 25
A PM1 12
B PM5 5445
B PM3 454
B PM14 74
B PM2 54
C PM12 4744
C PM27 521
C PM7 515
C PM28 48
*/
--End