oracle 3個實用統計sql情境，oracle統計sql情境

oracle 3個實用統計sql情境，oracle統計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  

 

結果：