我看到一个问题，求助一下各位。

[samfeng_2003]

有表的数据如此
Item_Name Manufacturer rank
Item 1 Manu_A 82
Item 2 Manu_A 65
Item 3 Manu_A 41
Item 4 Manu_B 32
Item 5 Manu_C 21
Item 6 Manu_B 19
现在希望得到如此的排序
Item 1 Manu_A 82
Item 4 Manu_B 32
Item 5 Manu_C 21
Item 2 Manu_A 65
Item 6 Manu_B 19
Item 3 Manu_A 41

对待这这些数据可以得到如此效果

SELECT T1.item_name, T1.manufacturer, T1.rank
FROM 表 AS T1
JOIN 表 AS T2
ON T1.manufacturer = T2.manufacturer
AND T1.rank <= T2.rank
GROUP BY T1.item_name, T1.manufacturer, T1.rank
ORDER BY COUNT(*), T1.rank DESC

但是一旦数据变为：
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

那么就出现这样的结果：
item_name manufacturer rank
-------------------- -------------------- -----------
Item 7 Manu_B 89
Item 1 Manu_A 82
Item 5 Manu_C 21
Item 2 Manu_A 60
Item 4 Manu_B 32
Item 6 Manu_B 19
Item 3 Manu_A 9

我现在想得到如此的算法，就是不管数据怎么变化，都得到

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
*/

[samfeng_2003]

呵呵！害我想了半天！:) 谢谢子陌兄了！

[vivianfdlpw]

declare @tb table
(
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 行）
*/

[samfeng_2003]

:( vivianfdlpw() 兄，不好意思，因为着急答案，就已经给分了！