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 2000select 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) col3from( select t.* , px = (select count(1) from tb where a = t.a and c > t.c) + 1 from tb t) mgroup by aunion allselect 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) col3from( select t.* , px = (select count(1) from tb where a = t.a and c > t.c) + 1 from tb t) mgroup by aorder by a , col1 --sql 2005select 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) col3from( select t.* , px = row_number() over(partition by a order by c desc) from tb t) mgroup by aunion allselect 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) col3from( select t.* , px = row_number() over(partition by a order by c desc) from tb t) mgroup by aorder by a , col1
------解决方案--------------------
- SQL code
--sql 2000select 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) col10from( select t.* , px = (select count(1) from tb where a = t.a and c > t.c) + 1 from tb t) mgroup by aunion allselect 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) col10from( select t.* , px = (select count(1) from tb where a = t.a and c > t.c) + 1 from tb t) mgroup by aorder by a , col1 --sql 2005select 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) col10from( select t.* , px = row_number() over(partition by a order by c desc) from tb t) mgroup by aunion allselect 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) col10from( select t.* , px = row_number() over(partition by a order by c desc) from tb t) mgroup by aorder by a , col1