--生成测试数据
create table #T(item_name varchar(20),manufacturer varchar(20),rank int)
insert into #T
select 'Item 1','Manu_A',82 union
select 'Item 2','Manu_A',60 union
select 'Item 3','Manu_A',9 union
select 'Item 4','Manu_B',32 union
select 'Item 5','Manu_C',21 union
select 'Item 6','Manu_B',19 union
select 'Item 7','Manu_B',89--执行查询
select
a.item_name,a.manufacturer,a.rank
from
#T a,
#T b
where
a.manufacturer = b.manufacturer
and
a.rank <= b.rank
group by
a.item_name,a.manufacturer,a.rank
order by
count(b.manufacturer),a.manufacturer
--输出结果
/*
item_name manufacturer rank
--------- ------------ ----
Item 1 Manu_A 82
Item 7 Manu_B 89
Item 5 Manu_C 21
Item 2 Manu_A 60
Item 4 Manu_B 32
Item 3 Manu_A 9
Item 6 Manu_B 19
*/
create table #T(item_name varchar(20),manufacturer varchar(20),rank int)
insert into #T
select 'Item 1','Manu_A',82 union
select 'Item 2','Manu_A',60 union
select 'Item 3','Manu_A',9 union
select 'Item 4','Manu_B',32 union
select 'Item 5','Manu_C',21 union
select 'Item 6','Manu_B',19 union
select 'Item 7','Manu_B',89--执行查询
select
a.item_name,a.manufacturer,a.rank
from
#T a,
#T b
where
a.manufacturer = b.manufacturer
and
a.rank <= b.rank
group by
a.item_name,a.manufacturer,a.rank
order by
count(b.manufacturer),a.manufacturer
--输出结果
/*
item_name manufacturer rank
--------- ------------ ----
Item 1 Manu_A 82
Item 7 Manu_B 89
Item 5 Manu_C 21
Item 2 Manu_A 60
Item 4 Manu_B 32
Item 3 Manu_A 9
Item 6 Manu_B 19
*/
(
item_name varchar(10),
manufacturer varchar(10),
rank int
)
insert @tb
select 'Item 1','Manu_A',82 union
select 'Item 2','Manu_A',60 union
select 'Item 3','Manu_A',9 union
select 'Item 4','Manu_B',32 union
select 'Item 5','Manu_C',21 union
select 'Item 6','Manu_B',19 union
select 'Item 7','Manu_B',89--查询
select item_name
,manufacturer
,rank
from
(
select top 100 percent *
,(select count(1) from @tb where manufacturer=t.manufacturer and rank>=t.rank) as 'order'
from @tb t
order by item_name,rank
)tb
order by [order],manufacturer--结果
/*item_name manufacturer rank
---------- ------------ -----------
Item 1 Manu_A 82.00
Item 7 Manu_B 89.00
Item 5 Manu_C 21.00
Item 2 Manu_A 60.00
Item 4 Manu_B 32.00
Item 3 Manu_A 9.00
Item 6 Manu_B 19.00(所影响的行数为 7 行)
*/