SQL Server进阶（8）查询——开窗函数、四大排名函数、透视数据、逆透视数据

cnki 2021-12-09 原文

概述

ROW_NUMBER() OVER(PARTITION BY CustId ORDER BY ID DESC)

https://www.jb51.net/article/75533.htm

开窗函数

SELECT empid, ordermonth, val,
  SUM(val) OVER(PARTITION BY empid
                ORDER BY ordermonth
                ROWS BETWEEN UNBOUNDED PRECEDING
                         AND CURRENT ROW) AS runval
FROM Sales.EmpOrders;

四大排名函数（ROW_NUMBER、RANK、DENSE_RANK、NTILE）

　　ROW_NUMBER()的用途的非常广泛，排序最好用他，一般可以用来实现web程序的分页，他会为查询出来的每一行记录生成一个序号，依次排序且不会重复，注意使用row_number函数时必须要用over子句选择对某一列进行排序才能生成序号。

　　rownum列就是row_number函数生成的序号列，其基本原理是先使用over子句中的排序语句对记录进行排序，然后按照这个顺序生成序号。over子句中的order by子句与SQL语句中的order by子句没有任何关系，这两处的order by 可以完全不同。

　　dense_rank函数的功能与rank函数类似，dense_rank函数在生成序号时是连续的，而rank函数生成的序号有可能不连续。dense_rank函数出现相同排名时，将不跳过相同排名号，rank值紧接上一次的rank值。在各个分组内，rank()是跳跃排序，有两个第一名时接下来就是第三名，dense_rank()是连续排序，有两个第一名时仍然跟着第二名。

SELECT orderid, custid, val,
  ROW_NUMBER() OVER(ORDER BY val) AS rownum,
  RANK()       OVER(ORDER BY val) AS rank,
  DENSE_RANK() OVER(ORDER BY val) AS dense_rank,
  NTILE(10)    OVER(ORDER BY val) AS ntile
FROM Sales.OrderValues
ORDER BY val;

偏移开窗函数

透视数据 (pivoting)——行旋转列

　　是一种把数据从行的状态旋转为列的状态的处理，在这个过程中可能需要对值进行聚合（有需求的话）。

create table tb(姓名 varchar(10),课程 varchar(10),分数 int)

insert into tb values(\'张三\',\'语文\',74)
                  
insert into tb values(\'张三\',\'数学\',83)
                  
insert into tb values(\'张三\',\'物理\',93)
                  
insert into tb values(\'李四\',\'语文\',74)
                  
insert into tb values(\'李四\',\'数学\',84)
                  
insert into tb values(\'李四\',\'物理\',94)

建立表格

select * from tb

select 姓名,
 max(case 课程 when\'语文\'then 分数 else 0 end)语文,
 max(case 课程 when\'数学\'then 分数 else 0 end)数学,
 max(case 课程 when\'物理\'then 分数 else 0 end)物理,
 sum(分数)总分,
 cast(avg(分数*1.0)as decimal(18,2))平均分
from dbo.tb
group by 姓名

--select * from tb pivot(max(分数) for 课程 in(语文,数学,物理)) a

select m.*,n.总分,n.平均分 from
(select * from tb pivot(max(分数) for 课程 in(语文,数学,物理))a)m,
(select 姓名,sum(分数)总分,cast(avg(分数*1.0)as decimal(18,2))平均分 from tb group by 姓名)n
where m.姓名=n.姓名