Question

(SQL SERVER）SQL 语句计算多列排名，如题：

有表如下：
select A1,A2,A3,... ,A10 from Table1

1.所有数值区间在0-100
2.假如有10条数据 A1 =100的有3条 A1=99的有2条 A1=98的有4条 A1=0的有1条 其他A2,A3,A4,A.... 均有相同数值的情况
3.排名规则：以2中A1为例 3条100 则3条数据在[A1排名]中并列 均为1 同时跳过第2第3 因此 2条99的 [A1排名]均为4 依次类推

如何得到[A1排名]至[A10排名]中各列的值
最后输出 A1,A2,A3,... ,A10,A1排名,A2排名,A3排名,... ,A10排名
求大神代码！！

Answer 1

with tt as(
select '88' A1,'9' A2
union all
select '55' A1,'9' A2
union all
select '55' A1,'4' A2
union all
select '99' A1,'4' A2
union all
select '99' A1,'1' A2
)
select A1,DENSE_RANK() OVER(ORDER BY A1 desc) as A_1,A2,DENSE_RANK() OVER(ORDER BY A2 desc) as A_2 from tt
select A1,RANK() OVER(ORDER BY A1 desc) as A_1,A2,RANK() OVER(ORDER BY A2 desc) as A_2 from tt

Answer 2

看一下sqlserver的查询分析函数