oracle 3个实用统计sql场景

我们使用oracle做一些统计的时候,时常碰到如下场景:

1.竖列转横列

2.分组并合并某列作为结果集

3.分组排序取首条记录

 

我们使用一个简化的业务场景,来展示这三个场景如何使用sql来解决。

业务场景:一张表记录着员工的出勤记录

业务需求:(对应上面的三个场景)

1.统计员工某年的每月出勤记录数

2.查询每个人的出勤记录

3.获得每个员工第一天上班的出勤记录

 

首先我们先创建测试数据表和测试数据

 

Sql代码  技术分享
  1. --创建考勤记录表  
  2. CREATE TABLE T_ATTENDANCE_LOG  
  3. (    
  4.     ID_ VARCHAR(36),    
  5.     USERNAME_ VARCHAR(255),    
  6.     LOGDATE_ VARCHAR(100)  
  7. )    
  8.   
  9.   
  10. --初始化一些测试数据  
  11. insert into T_ATTENDANCE_LOG (ID_,USERNAME_,LOGDATE_) VALUES (‘1‘,‘张三‘,‘2014-02-01‘);  
  12. insert into T_ATTENDANCE_LOG (ID_,USERNAME_,LOGDATE_) VALUES (‘2‘,‘张三‘,‘2014-02-02‘);  
  13. insert into T_ATTENDANCE_LOG (ID_,USERNAME_,LOGDATE_) VALUES (‘3‘,‘张三‘,‘2014-02-03‘);  
  14. insert into T_ATTENDANCE_LOG (ID_,USERNAME_,LOGDATE_) VALUES (‘4‘,‘张三‘,‘2014-02-04‘);  
  15. insert into T_ATTENDANCE_LOG (ID_,USERNAME_,LOGDATE_) VALUES (‘5‘,‘张三‘,‘2014-02-05‘);  
  16. insert into T_ATTENDANCE_LOG (ID_,USERNAME_,LOGDATE_) VALUES (‘6‘,‘张三‘,‘2014-02-06‘);  
  17.   
  18. insert into T_ATTENDANCE_LOG (ID_,USERNAME_,LOGDATE_) VALUES (‘11‘,‘李四‘,‘2014-03-01‘);  
  19. insert into T_ATTENDANCE_LOG (ID_,USERNAME_,LOGDATE_) VALUES (‘12‘,‘李四‘,‘2014-04-01‘);  
  20. insert into T_ATTENDANCE_LOG (ID_,USERNAME_,LOGDATE_) VALUES (‘13‘,‘李四‘,‘2014-05-01‘);  
  21.   
  22. insert into T_ATTENDANCE_LOG (ID_,USERNAME_,LOGDATE_) VALUES (‘21‘,‘王五‘,‘2014-02-15‘);  
  23. insert into T_ATTENDANCE_LOG (ID_,USERNAME_,LOGDATE_) VALUES (‘22‘,‘王五‘,‘2014-03-15‘);  
  24.   
  25. --查询  
  26. SELECT T.*,T.ROWID FROM  T_ATTENDANCE_LOG T;  

 

结果:

技术分享

 

 

1.统计员工2014年的每月出勤情况

Sql代码  技术分享
  1. with sql1 as  
  2. (  
  3. select USERNAME_,substr(LOGDATE_,0,7) as a,count(LOGDATE_) as b from T_ATTENDANCE_LOG  
  4. group by USERNAME_,substr(LOGDATE_,0,7)  
  5. )  
  6. select USERNAME_,   
  7. sum(case A when ‘2014-01‘ then B end) 一月,  
  8. sum(case A when ‘2014-02‘ then B end) 二月,  
  9. sum(case A when ‘2014-03‘ then B end) 三月,  
  10. sum(case A when ‘2014-04‘ then B end) 四月,  
  11. sum(case A when ‘2014-05‘ then B end) 五月,    
  12. sum(case A  when ‘2014-06‘ then B  end) 六月,   
  13. sum(case A  when ‘2014-07‘ then B  end) 七月,   
  14. sum(case A  when ‘2014-08‘ then B  end) 八月,   
  15. sum(case A  when ‘2014-09‘ then B  end) 九月,   
  16. sum(case A  when ‘2014-10‘ then B  end) 十月,   
  17. sum(case A  when ‘2014-11‘ then B  end) 十一月,   
  18. sum(case A  when ‘2014-12‘ then B  end) 十二月  
  19. from sql1  group by USERNAME_  

 这里用到“sql统计利器”--with。

 

结果:

技术分享
 

 

2.查询每个人的出勤记录

Sql代码  技术分享
  1. select USERNAME_ as 员工,wmsys.wm_concat(LOGDATE_) as 出勤记录 from T_ATTENDANCE_LOG t group by  USERNAME_  

 

结果:

技术分享
 

但是我们发现这个统计出来的结果是乱序,改造一下

Sql代码  技术分享
  1. select USERNAME_ as 员工, max(r) as 出勤记录 from (  
  2. select USERNAME_,wmsys.wm_concat(LOGDATE_) OVER(PARTITION BY USERNAME_ ORDER BY LOGDATE_) r  
  3. from T_ATTENDANCE_LOG t   
  4. )group by USERNAME_  

 

改造结果:

技术分享
 

 

3.获得每个员工第一天上班的出勤记录

Sql代码  技术分享
  1. SELECT * FROM   
  2. (  
  3. --分组排序加序号  
  4. select USERNAME_,LOGDATE_,ROW_NUMBER() OVER(PARTITION BY USERNAME_ ORDER BY LOGDATE_) r  
  5. from T_ATTENDANCE_LOG t   
  6. group by USERNAME_,LOGDATE_  
  7. where R=1  

 

结果:

技术分享

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。