oracle 3个实用统计sql场景

最新推荐文章于 2024-08-20 21:39:14 发布

最是那一低头的温柔

最新推荐文章于 2024-08-20 21:39:14 发布

阅读量2k

点赞数

分类专栏： Oracle

Oracle 专栏收录该内容

76 篇文章 1 订阅

订阅专栏

数据库技术学习 https://www.itkc8.com

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

1.竖列转横列

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

3.分组排序取首条记录

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

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

业务需求：（对应上面的三个场景）

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

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

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

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

Sql代码

--创建考勤记录表

2.CREATE TABLE T_ATTENDANCE_LOG  
3.(    
4.    ID_ VARCHAR(36),    
5.    USERNAME_ VARCHAR(255),    
6.    LOGDATE_ VARCHAR(100)  
7.)    
8.

--初始化一些测试数据

2.insert into T_ATTENDANCE_LOG (ID_,USERNAME_,LOGDATE_) VALUES ('1','张三','2014-02-01');  
3.insert into T_ATTENDANCE_LOG (ID_,USERNAME_,LOGDATE_) VALUES ('2','张三','2014-02-02');  
4.insert into T_ATTENDANCE_LOG (ID_,USERNAME_,LOGDATE_) VALUES ('3','张三','2014-02-03');  
5.insert into T_ATTENDANCE_LOG (ID_,USERNAME_,LOGDATE_) VALUES ('4','张三','2014-02-04');  
6.insert into T_ATTENDANCE_LOG (ID_,USERNAME_,LOGDATE_) VALUES ('5','张三','2014-02-05');  
7.insert into T_ATTENDANCE_LOG (ID_,USERNAME_,LOGDATE_) VALUES ('6','张三','2014-02-06');  
8.  
9.insert into T_ATTENDANCE_LOG (ID_,USERNAME_,LOGDATE_) VALUES ('11','李四','2014-03-01');  
10.insert into T_ATTENDANCE_LOG (ID_,USERNAME_,LOGDATE_) VALUES ('12','李四','2014-04-01');  
11.insert into T_ATTENDANCE_LOG (ID_,USERNAME_,LOGDATE_) VALUES ('13','李四','2014-05-01');  
12.  
13.insert into T_ATTENDANCE_LOG (ID_,USERNAME_,LOGDATE_) VALUES ('21','王五','2014-02-15');  
14.insert into T_ATTENDANCE_LOG (ID_,USERNAME_,LOGDATE_) VALUES ('22','王五','2014-03-15');

--查询

SELECT T.*,T.ROWID FROM  T_ATTENDANCE_LOG T;

--创建考勤记录表
CREATE TABLE T_ATTENDANCE_LOG
(  
    ID_ VARCHAR(36),  
    USERNAME_ VARCHAR(255),  
    LOGDATE_ VARCHAR(100)
)  


--初始化一些测试数据
insert into T_ATTENDANCE_LOG (ID_,USERNAME_,LOGDATE_) VALUES ('1','张三','2014-02-01');
insert into T_ATTENDANCE_LOG (ID_,USERNAME_,LOGDATE_) VALUES ('2','张三','2014-02-02');
insert into T_ATTENDANCE_LOG (ID_,USERNAME_,LOGDATE_) VALUES ('3','张三','2014-02-03');
insert into T_ATTENDANCE_LOG (ID_,USERNAME_,LOGDATE_) VALUES ('4','张三','2014-02-04');
insert into T_ATTENDANCE_LOG (ID_,USERNAME_,LOGDATE_) VALUES ('5','张三','2014-02-05');
insert into T_ATTENDANCE_LOG (ID_,USERNAME_,LOGDATE_) VALUES ('6','张三','2014-02-06');

insert into T_ATTENDANCE_LOG (ID_,USERNAME_,LOGDATE_) VALUES ('11','李四','2014-03-01');
insert into T_ATTENDANCE_LOG (ID_,USERNAME_,LOGDATE_) VALUES ('12','李四','2014-04-01');
insert into T_ATTENDANCE_LOG (ID_,USERNAME_,LOGDATE_) VALUES ('13','李四','2014-05-01');

insert into T_ATTENDANCE_LOG (ID_,USERNAME_,LOGDATE_) VALUES ('21','王五','2014-02-15');
insert into T_ATTENDANCE_LOG (ID_,USERNAME_,LOGDATE_) VALUES ('22','王五','2014-03-15');

--查询
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_

with sql1 as
(
select USERNAME_,substr(LOGDATE_,0,7) as a,count(LOGDATE_) as b from T_ATTENDANCE_LOG
group by USERNAME_,substr(LOGDATE_,0,7)
)
select USERNAME_, 
sum(case A when '2014-01' then B end) 一月,
sum(case A when '2014-02' then B end) 二月,
sum(case A when '2014-03' then B end) 三月,
sum(case A when '2014-04' then B end) 四月,
sum(case A when '2014-05' then B end) 五月,  
sum(case A  when '2014-06' then B  end) 六月, 
sum(case A  when '2014-07' then B  end) 七月, 
sum(case A  when '2014-08' then B  end) 八月, 
sum(case A  when '2014-09' then B  end) 九月, 
sum(case A  when '2014-10' then B  end) 十月, 
sum(case A  when '2014-11' then B  end) 十一月, 
sum(case A  when '2014-12' then B  end) 十二月
from sql1  group by USERNAME_

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

结果：

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

Sql代码

select USERNAME_ as 员工,wmsys.wm_concat(LOGDATE_) as 出勤记录 from T_ATTENDANCE_LOG t group by  USERNAME_

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_

select USERNAME_ as 员工, max(r) as 出勤记录 from (
select USERNAME_,wmsys.wm_concat(LOGDATE_) OVER(PARTITION BY USERNAME_ ORDER BY LOGDATE_) r
from T_ATTENDANCE_LOG t 
)group by USERNAME_

改造结果：

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

Sql代码

SELECT * FROM 
(
--分组排序加序号
select USERNAME_,LOGDATE_,ROW_NUMBER() OVER(PARTITION BY USERNAME_ ORDER BY LOGDATE_) r
from T_ATTENDANCE_LOG t 
group by USERNAME_,LOGDATE_
) where R=1

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

结果：