不同学科同时排班名次和校名次问题
原始数据如下：
班姓名语文数学总分
1张三8081
1张四8179
1王五7966
2钱七6689
2孙八8977
2周九7753
3吴十5391
3钱二9142
3杨一4274
现在想对各科作如下排序，包括计算总分
班姓名语文班名次校名次数学班名次校名次总分班名次校名次
1张三8081
1张四8179
1王五7966
2钱七6689
2孙八8977
2周九7753
3吴十5391
3钱二9142
3杨一4274
求高手给出SQL语句学习下
[解决办法]
declare @T table (班 int,姓名 varchar(4),语文 int,数学 int,总分 sql_variant)insert into @Tselect 1,'张三',80,81,null union allselect 1,'张四',81,79,null union allselect 1,'王五',79,66,null union allselect 2,'钱七',66,89,null union allselect 2,'孙八',89,77,null union allselect 2,'周九',77,53,null union allselect 3,'吴十',53,91,null union allselect 3,'钱二',91,42,null union allselect 3,'杨一',42,74,nullselect 班,姓名,语文,班名次=(select count(1) from @T where 班=t.班 and 语文>=t.语文),校名次=(select count(1) from @T where 语文>=t.语文),数学,班名次=(select count(1) from @T where 班=t.班 and 数学>=t.数学),校名次=(select count(1) from @T where 数学>=t.数学),总分=语文+数学,班名次=(select count(1) from @T where 班=t.班 and 语文+数学>=t.语文+t.数学),校名次=(select count(1) from @T where 语文+数学>=t.语文+t.数学)from @T t/*班 姓名 语文 班名次 校名次 数学 班名次 校名次 总分 班名次 校名次----------- ---- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------1 张三 80 2 4 81 1 3 161 1 21 张四 81 1 3 79 2 4 160 2 31 王五 79 3 5 66 3 7 145 3 52 钱七 66 3 7 89 1 2 155 2 42 孙八 89 1 2 77 2 5 166 1 12 周九 77 2 6 53 3 8 130 3 83 吴十 53 2 8 91 1 1 144 1 63 钱二 91 1 1 42 3 9 133 2 73 杨一 42 3 9 74 2 6 116 3 9*/
[解决办法]
create table yxdxcy(班 int, 姓名 char(6), 语文 int, 数学 int, 总分 int)insert into yxdxcy(班,姓名,语文,数学)select 1, '张三', 80, 81 union all select 1, '张四', 81, 79 union all select 1, '王五', 79, 66 union all select 2, '钱七', 66, 89 union all select 2, '孙八', 89, 77 union all select 2, '周九', 77, 53 union all select 3, '吴十', 53, 91 union all select 3, '钱二', 91, 42 union all select 3, '杨一', 42, 74 select a.班, a.姓名, a.语文, b.rn '班名次', c.rn '校名次',a.数学, d.rn '班名次', e.rn '校名次',a.语文+a.数学 '总分', f.rn '班名次', g.rn '校名次'from yxdxcy ainner join(select 班,姓名,row_number() over(partition by 班 order by 语文 desc) rn from yxdxcy) bon a.班=b.班 and a.姓名=b.姓名inner join(select 班,姓名,row_number() over(order by 语文 desc) rn from yxdxcy) con a.班=c.班 and a.姓名=c.姓名inner join(select 班,姓名,row_number() over(partition by 班 order by 数学 desc) rn from yxdxcy) don a.班=d.班 and a.姓名=d.姓名inner join(select 班,姓名,row_number() over(order by 数学 desc) rn from yxdxcy) eon a.班=e.班 and a.姓名=e.姓名inner join(select 班,姓名,row_number() over(partition by 班 order by 语文+数学 desc) rn from yxdxcy) fon a.班=f.班 and a.姓名=f.姓名inner join(select 班,姓名,row_number() over(order by 语文+数学 desc) rn from yxdxcy) gon a.班=g.班 and a.姓名=g.姓名班 姓名 语文 班名次 校名次 数学 班名次 校名次 总分 班名次 校名次----------- ------ ----------- -------------------- -------------------- ----------- -------------------- -------------------- ----------- -------------------- --------------------1 张四 81 1 3 79 2 4 160 2 31 张三 80 2 4 81 1 3 161 1 21 王五 79 3 5 66 3 7 145 3 52 孙八 89 1 2 77 2 5 166 1 12 周九 77 2 6 53 3 8 130 3 82 钱七 66 3 7 89 1 2 155 2 43 钱二 91 1 1 42 3 9 133 2 73 吴十 53 2 8 91 1 1 144 1 63 杨一 42 3 9 74 2 6 116 3 9(9 row(s) affected)
[解决办法]
---By 苦苦的潜行者create table t1(班 int,姓名 varchar(10),语文 int,数学 int,总分 int)insert t1select 1, '张三', 80, 81,null union all select 1, '张四', 81, 79,null union all select 1, '王五', 79, 66,null union allselect 2, '钱七', 66, 89,null union allselect 2, '孙八', 89, 77,null union allselect 2, '周九', 77, 53,null union allselect 3, '吴十', 53, 91,null union allselect 3, '钱二', 91, 42,null union allselect 3, '杨一', 42, 74,null go;with sumscore(班,语文,数学,总分) as( select 班,SUM(语文),SUM(数学),SUM(语文+数学) from t1 group by 班),hashtable(班,姓名,语文,语文班名次,数学,数学班名次,总分,总分班名次,语文总分,数学总分,所有总分)as(select a.班 ,a.姓名 ,a.语文,语文班名次=ROW_NUMBER()over(order by a.语文 desc),a.数学 ,数学班名次=ROW_NUMBER()over(order by a.数学 desc ),总分=a.语文+a.数学,总分班名次=ROW_NUMBER()over(order by a.语文+a.数学 desc),b.语文 ,b.数学 ,b.总分 from t1 aleft join sumscore b on b.班=a.班 where a.班=1union allselect a.班 ,a.姓名 ,a.语文,语文班名次=ROW_NUMBER()over(order by a.语文 desc),a.数学 ,数学班名次=ROW_NUMBER()over(order by a.数学 desc ),总分=a.语文+a.数学,总分班名次=ROW_NUMBER()over(order by a.语文+a.数学 desc),b.语文 ,b.数学 ,b.总分 from t1 aleft join sumscore b on b.班=a.班 where a.班=2union allselect a.班 ,a.姓名 ,a.语文,语文班名次=ROW_NUMBER()over(order by a.语文 desc),a.数学 ,数学班名次=ROW_NUMBER()over(order by a.数学 desc ),总分=a.语文+a.数学,总分班名次=ROW_NUMBER()over(order by a.语文+a.数学 desc),b.语文 ,b.数学 ,b.总分 from t1 aleft join sumscore b on b.班=a.班 where a.班=3--select o.班,o.姓名,o.语文,o.语文班名次,o.数学,o.数学班名次,o.总分,o.总分班名次,o.语文总分,o.数学总分,o.所有总分 --from hashtable o--join sumscore p on o.班=p.班 --where o.班<=3 )select a.班 ,a.姓名 ,a.语文,a.语文班名次 ,语文校名次=ROW_NUMBER()over(order by a.语文 desc),a.数学,a.数学班名次 ,数学校名次=ROW_NUMBER()over(order by a.数学 desc),a.总分,a.总分班名次 ,总分校名次=ROW_NUMBER()over(order by a.总分 desc)from hashtable a order by a.班 /*班 姓名 语文 语文班名次 语文校名次 数学 数学班名次 总分 总分班名次 总分校名次-- -- -- -------- ------ --- ------ --- ------- --------1 张三 80 2 4 81 1 3 161 1 21 张四 81 1 3 79 2 4 160 2 31 王五 79 3 5 66 3 7 145 3 52 钱七 66 3 7 89 1 2 155 1 42 周九 77 2 6 53 3 8 130 3 82 孙八 89 1 2 77 2 5 166 2 13 杨一 42 3 9 74 2 6 116 2 93 吴十 53 2 8 91 1 1 144 1 63 钱二 91 1 1 42 3 9 133 3 7*/godrop table t1
