五维思考

学习要加,骄傲要减,机会要乘,懒惰要除。 http://www.5dthink.cn

  博客园 :: 首页 :: 博问 :: 闪存 :: 新随笔 :: 联系 :: 订阅 订阅 :: 管理 ::

 

 

-- 原表数据
SELECT * FROM dbo.Student

S#         Sname      Sage                    Ssex
---------- ---------- ----------------------- ----------
01         赵雷         1990-01-01 00:00:00.00002         钱电         1990-12-21 00:00:00.00003         孙风         1990-05-20 00:00:00.00004         李云         1990-08-06 00:00:00.00005         周梅         1991-12-01 00:00:00.00006         吴兰         1992-03-01 00:00:00.00007         郑竹         1989-07-01 00:00:00.00008         王菊         1990-01-20 00:00:00.000-- MAX(),最大年龄
SELECT *,MAX(DATEDIFF(yyyy,Sage,GETDATE()))OVER() MaxSage FROM dbo.Student

S#         Sname      Sage                    Ssex       MaxSage
---------- ---------- ----------------------- ---------- -----------
01         赵雷         1990-01-01 00:00:00.00028
02         钱电         1990-12-21 00:00:00.00028
03         孙风         1990-05-20 00:00:00.00028
04         李云         1990-08-06 00:00:00.00028
05         周梅         1991-12-01 00:00:00.00028
06         吴兰         1992-03-01 00:00:00.00028
07         郑竹         1989-07-01 00:00:00.00028
08         王菊         1990-01-20 00:00:00.00028
 
-- COUNT(),总人数
SELECT *,COUNT(S#)OVER() 总人数 FROM dbo.Student

S#         Sname      Sage                    Ssex       总人数
---------- ---------- ----------------------- ---------- -----------
01         赵雷         1990-01-01 00:00:00.0008
02         钱电         1990-12-21 00:00:00.0008
03         孙风         1990-05-20 00:00:00.0008
04         李云         1990-08-06 00:00:00.0008
05         周梅         1991-12-01 00:00:00.0008
06         吴兰         1992-03-01 00:00:00.0008
07         郑竹         1989-07-01 00:00:00.0008
08         王菊         1990-01-20 00:00:00.0008
 
-- Partition By 分组统计数量
-- 根据性别分组后,统计
SELECT *,COUNT(*) OVER(PARTITION BY Ssex) 总数 FROM dbo.Student

S#         Sname      Sage                    Ssex       总数
---------- ---------- ----------------------- ---------- -----------
01         赵雷         1990-01-01 00:00:00.0004
02         钱电         1990-12-21 00:00:00.0004
03         孙风         1990-05-20 00:00:00.0004
04         李云         1990-08-06 00:00:00.0004
05         周梅         1991-12-01 00:00:00.0004
06         吴兰         1992-03-01 00:00:00.0004
07         郑竹         1989-07-01 00:00:00.0004
08         王菊         1990-01-20 00:00:00.0004
 
-- 根据性别分组后,统计、排序
SELECT *,COUNT(*) OVER(PARTITION BY Ssex ORDER BY Sname) 序号 FROM dbo.Student

S#         Sname      Sage                    Ssex       序号
---------- ---------- ----------------------- ---------- -----------
04         李云         1990-08-06 00:00:00.0001
02         钱电         1990-12-21 00:00:00.0002
03         孙风         1990-05-20 00:00:00.0003
01         赵雷         1990-01-01 00:00:00.0004
08         王菊         1990-01-20 00:00:00.0001
06         吴兰         1992-03-01 00:00:00.0002
07         郑竹         1989-07-01 00:00:00.0003
05         周梅         1991-12-01 00:00:00.0004
 
-- AVG(),平均年龄
SELECT *,AVG(DATEDIFF(yyyy,Sage,GETDATE()))OVER() 平均年龄 FROM dbo.Student

S#         Sname      Sage                    Ssex       平均年龄
---------- ---------- ----------------------- ---------- -----------
01         赵雷         1990-01-01 00:00:00.00026
02         钱电         1990-12-21 00:00:00.00026
03         孙风         1990-05-20 00:00:00.00026
04         李云         1990-08-06 00:00:00.00026
05         周梅         1991-12-01 00:00:00.00026
06         吴兰         1992-03-01 00:00:00.00026
07         郑竹         1989-07-01 00:00:00.00026
08         王菊         1990-01-20 00:00:00.00026
 
--ROW_NUMBER(),计数
SELECT *,ROW_NUMBER()OVER(ORDER BY S# DESC) 序号 FROM dbo.Student

S#         Sname      Sage                    Ssex       序号
---------- ---------- ----------------------- ---------- --------------------
08         王菊         1990-01-20 00:00:00.0001
07         郑竹         1989-07-01 00:00:00.0002
06         吴兰         1992-03-01 00:00:00.0003
05         周梅         1991-12-01 00:00:00.0004
04         李云         1990-08-06 00:00:00.0005
03         孙风         1990-05-20 00:00:00.0006
02         钱电         1990-12-21 00:00:00.0007
01         赵雷         1990-01-01 00:00:00.0008
 
--Row_Rumber(),实现分页效果
WITH T AS ( 
    SELECT ROW_NUMBER() OVER ( ORDER BY S# DESC ) RowNumber ,*
    FROM dbo.Student
)
SELECT * FROM T WHERE T.RowNumber BETWEEN 1 AND 3

RowNumber            S#         Sname      Sage                    Ssex
-------------------- ---------- ---------- ----------------------- ----------
1                    08         王菊         1990-01-20 00:00:00.0002                    07         郑竹         1989-07-01 00:00:00.0003                    06         吴兰         1992-03-01 00:00:00.000--Rank() 排名函数,名次相同,跳过
SELECT *,RANK()OVER(ORDER BY Ssex) 名次 FROM dbo.Student
S#         Sname      Sage                    Ssex       名次
---------- ---------- ----------------------- ---------- --------------------
01         赵雷         1990-01-01 00:00:00.0001
02         钱电         1990-12-21 00:00:00.0001
03         孙风         1990-05-20 00:00:00.0001
04         李云         1990-08-06 00:00:00.0001
05         周梅         1991-12-01 00:00:00.0005
06         吴兰         1992-03-01 00:00:00.0005
07         郑竹         1989-07-01 00:00:00.0005
08         王菊         1990-01-20 00:00:00.0005
 
--DENSE_Rank() 排名函数,名次相同不跳过
SELECT *,DENSE_RANK()OVER(ORDER BY Ssex) 名次 FROM dbo.Student

S#         Sname      Sage                    Ssex       名次
---------- ---------- ----------------------- ---------- --------------------
01         赵雷         1990-01-01 00:00:00.0001
02         钱电         1990-12-21 00:00:00.0001
03         孙风         1990-05-20 00:00:00.0001
04         李云         1990-08-06 00:00:00.0001
05         周梅         1991-12-01 00:00:00.0002
06         吴兰         1992-03-01 00:00:00.0002
07         郑竹         1989-07-01 00:00:00.0002
08         王菊         1990-01-20 00:00:00.0002
 
-- NTILE()函数,参数:记录总数/划分区域 = 每个区域数组,把记录序号放进数组 (平均分组)
SELECT *,NTILE(3)OVER(ORDER BY Ssex) 区域 FROM dbo.Student

S#         Sname      Sage                    Ssex       区域
---------- ---------- ----------------------- ---------- --------------------
01         赵雷         1990-01-01 00:00:00.0001
02         钱电         1990-12-21 00:00:00.0001
03         孙风         1990-05-20 00:00:00.0001
04         李云         1990-08-06 00:00:00.0002
05         周梅         1991-12-01 00:00:00.0002
06         吴兰         1992-03-01 00:00:00.0002
07         郑竹         1989-07-01 00:00:00.0003
08         王菊         1990-01-20 00:00:00.0003

-- FIRST_VALUE() 第一个
select *,first_value(sname)over(partition by Ssex order by S) [First Value] from Student

S  Sname Sage               Ssex First Value
-- ----- ------------------ ---- ----------
01 赵雷      1990/1/1 0:00:00 男    赵雷        
02 钱电    1990/12/21 0:00:00 男    赵雷        
03 孙风     1990/5/20 0:00:00 男    赵雷        
04 李云      1990/8/6 0:00:00 男    赵雷        
09 宋江    1971/3/15 15:56:30 男    赵雷        
05 周梅     1991/12/1 0:00:00 女    周梅        
06 吴兰      1992/3/1 0:00:00 女    周梅        
07 郑竹      1989/7/1 0:00:00 女    周梅        
08 王菊     1990/1/20 0:00:00 女    周梅  

-- LAST_VALUE() 最后一个
select *,
last_value(S)over(partition by Ssex order by S) [LAST-1],
last_value(S)over(partition by Ssex order by S desc) [LAST-2],
last_value(S)over(partition by Ssex order by S ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) [LAST-3]
from Student

S  Sname Sage               Ssex LAST-1 LAST-2 LAST-3
-- ----- ------------------ ---- ------ ------ ------
09 宋江    1971/3/15 15:56:3009     09     09    
04 李云      1990/8/6 0:00:0004     04     09    
03 孙风     1990/5/20 0:00:0003     03     09    
02 钱电    1990/12/21 0:00:0002     02     09    
01 赵雷      1990/1/1 0:00:0001     01     09    
08 王菊     1990/1/20 0:00:0008     08     08    
07 郑竹      1989/7/1 0:00:0007     07     08    
06 吴兰      1992/3/1 0:00:0006     06     08    
05 周梅     1991/12/1 0:00:0005     05     08    
--说明: 上面结果有点怪,可以这样理解LAST_VALUE()默认统计范围是 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

-- LAG() 上一个
select *,lag(sname)over(partition by Ssex order by S) [上一个] from Student

S  Sname Sage               Ssex 上一个 
-- ----- ------------------ ---- ----
01 赵雷      1990/1/1 0:00:00NULL
02 钱电    1990/12/21 0:00:00 男    赵雷  
03 孙风     1990/5/20 0:00:00 男    钱电  
04 李云      1990/8/6 0:00:00 男    孙风  
09 宋江    1971/3/15 15:56:30 男    李云  
05 周梅     1991/12/1 0:00:00NULL
06 吴兰      1992/3/1 0:00:00 女    周梅  
07 郑竹      1989/7/1 0:00:00 女    吴兰  
08 王菊     1990/1/20 0:00:00 女    郑竹  

-- LEAD() 下一个
select *,lead(sname)over(partition by Ssex order by S) [下一个] from Student

S  Sname Sage               Ssex 下一个 
-- ----- ------------------ ---- ----
01 赵雷      1990/1/1 0:00:00 男    钱电  
02 钱电    1990/12/21 0:00:00 男    孙风  
03 孙风     1990/5/20 0:00:00 男    李云  
04 李云      1990/8/6 0:00:00 男    宋江  
09 宋江    1971/3/15 15:56:30NULL
05 周梅     1991/12/1 0:00:00 女    吴兰  
06 吴兰      1992/3/1 0:00:00 女    郑竹  
07 郑竹      1989/7/1 0:00:00 女    王菊  
08 王菊     1990/1/20 0:00:00NULL
 

 

posted on 2017-02-06 10:19  五维思考  阅读(589)  评论(0编辑  收藏  举报

QQ群:1. 全栈码农【346906288】2. VBA/VSTO【2660245】