相加后的排列,多谢

求助相加后的排列,谢谢
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 2 1 3
1 17 15 12
3 6 7
3 17 13
2 5 4
2 16 12

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

------解决方案--------------------
--后面一段看懂了,前面那个A后面跟随十个数字的东西没看懂.

SQL code

--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
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 c else null end) col1,
       max(case px when 2 then c else null end) col2,
       max(case px when 3 then c else null end) col3
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
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 c else null end) col1,
       max(case px when 2 then c else null end) col2,
       max(case px when 3 then c else null end) col3
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

------解决方案--------------------

SQL code

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

相加后的排列,多谢

相关推荐