求助相加后的排列10名
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的值.
谢谢大家的帮助.
[解决办法]
if object_id('[tb]') is not null drop table [tb]gocreate table [tb]([A] int,[B] int,[C] int)insert [tb]select 1,1,15 union allselect 1,2,17 union allselect 1,3,12 union allselect 2,4,12 union allselect 2,5,16 union allselect 3,6,17 union allselect 3,7,13go;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) torder 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 01 2 1 3 0 0 0 0 0 0 03 17 13 0 0 0 0 0 0 0 03 6 7 0 0 0 0 0 0 0 02 16 12 0 0 0 0 0 0 0 02 5 4 0 0 0 0 0 0 0 0(6 行受影响)*/
[解决办法]
--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