求助相加后的排列10名

[freescy2002]

A B C
1 1 15
1 2 17
1 3 12
2 4 12
2 5 16
3 6 17
3 7 13
...

A 1 2 3 4 5 6 7 8 9 10
1 17 15 12
1 2 1 3
3 17 13
3 6 7
2 16 12
2 5 4
...

大概意思是这样子A B C三列
结果 A列
当A相同时计算C的和,按C和的大到小排列 显示A就是结果中的 1 3 2 ...
然后在A=1时按C最大排列,如果当时C有很多值,最多排10个值 ,也就是排10名的意思,然后再输出一列此时B的值.
谢谢大家的帮助.

[Walton_Yan]

不太明白题目的意思

[百年树人]

if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([A] int,[B] int,[C] int)
insert [tb]
select 1,1,15 union all
select 1,2,17 union all
select 1,3,12 union all
select 2,4,12 union all
select 2,5,16 union all
select 3,6,17 union all
select 3,7,13
go

;with t1 as(
select *,rn=row_number() over(partition by a order by c desc) from [tb]
)
select a,[1],[2],[3],[4],[5],[6],[7],[8],[9],[10]
from(
  select 1 as px2,a,max(case when rn=1 then c else 0 end) as [1],
  max(case when rn=2 then c else 0 end) as [2],
  max(case when rn=3 then c else 0 end) as [3],
  max(case when rn=4 then c else 0 end) as [4],
  max(case when rn=5 then c else 0 end) as [5],
  max(case when rn=6 then c else 0 end) as [6],
  max(case when rn=7 then c else 0 end) as [7],
  max(case when rn=8 then c else 0 end) as [8],
  max(case when rn=9 then c else 0 end) as [9],
  max(case when rn=10 then c else 0 end) as [10],
  sum(c) as px1
  from t1 group by a
  union all
  select 2,a,max(case when rn=1 then b else 0 end) as [1],
  max(case when rn=2 then b else 0 end) as [2],
  max(case when rn=3 then b else 0 end) as [3],
  max(case when rn=4 then b else 0 end) as [4],
  max(case when rn=5 then b else 0 end) as [5],
  max(case when rn=6 then b else 0 end) as [6],
  max(case when rn=7 then b else 0 end) as [7],
  max(case when rn=8 then b else 0 end) as [8],
  max(case when rn=9 then b else 0 end) as [9],
  max(case when rn=10 then b else 0 end) as [10],
  sum(c)
  from t1 group by a
) t
order by px1 desc,a,px2

/*
a           1           2           3           4           5           6           7           8           9           10
----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
1           17          15          12          0           0           0           0           0           0           0
1           2           1           3           0           0           0           0           0           0           0
3           17          13          0           0           0           0           0           0           0           0
3           6           7           0           0           0           0           0           0           0           0
2           16          12          0           0           0           0           0           0           0           0
2           5           4           0           0           0           0           0           0           0           0

(6 行受影响)

*/

[dawugui]

--sql 2000

select a , 
       max(case px when 1 then b else null end) col1,
       max(case px when 2 then b else null end) col2,
       max(case px when 3 then b else null end) col3,
       max(case px when 4 then b else null end) col4,
       max(case px when 5 then b else null end) col5,
       max(case px when 6 then b else null end) col6,
       max(case px when 7 then b else null end) col7,
       max(case px when 8 then b else null end) col8,
       max(case px when 9 then b else null end) col9,
       max(case px when 10 then b else null end) col10
from
(
  select t.* , px = (select count(1) from tb where a = t.a and c > t.c) + 1 from tb t
) m
group by a
union all
select a , 
       max(case px when 1 then b else null end) col1,
       max(case px when 2 then b else null end) col2,
       max(case px when 3 then b else null end) col3,
       max(case px when 4 then b else null end) col4,
       max(case px when 5 then b else null end) col5,
       max(case px when 6 then b else null end) col6,
       max(case px when 7 then b else null end) col7,
       max(case px when 8 then b else null end) col8,
       max(case px when 9 then b else null end) col9,
       max(case px when 10 then b else null end) col10
from
(
  select t.* , px = (select count(1) from tb where a = t.a and c > t.c) + 1 from tb t
) m
group by a
order by a , col1 

--sql 2005
select a , 
       max(case px when 1 then b else null end) col1,
       max(case px when 2 then b else null end) col2,
       max(case px when 3 then b else null end) col3,
       max(case px when 4 then b else null end) col4,
       max(case px when 5 then b else null end) col5,
       max(case px when 6 then b else null end) col6,
       max(case px when 7 then b else null end) col7,
       max(case px when 8 then b else null end) col8,
       max(case px when 9 then b else null end) col9,
       max(case px when 10 then b else null end) col10
from
(
  select t.* , px = row_number() over(partition by a order by c desc) from tb t
) m
group by a
union all
select a , 
       max(case px when 1 then b else null end) col1,
       max(case px when 2 then b else null end) col2,
       max(case px when 3 then b else null end) col3,
       max(case px when 4 then b else null end) col4,
       max(case px when 5 then b else null end) col5,
       max(case px when 6 then b else null end) col6,
       max(case px when 7 then b else null end) col7,
       max(case px when 8 then b else null end) col8,
       max(case px when 9 then b else null end) col9,
       max(case px when 10 then b else null end) col10
from
(
  select t.* , px = row_number() over(partition by a order by c desc) from tb t
) m
group by a
order by a , col1