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;  
--创建考勤记录表
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代码   
  1. 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代码   
  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  
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

 

结果:


  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
### 回答1: Oracle案例培训是为了提高学员在Oracle数据库领域的应用能力,使其能够更加熟练地应用各种Oracle数据库技术解决实际问题。在培训过程中,学员将会学习到Oracle对于数据库设计、数据模型设计、SQL语言的应用、PL/SQL编程以及Oracle数据库维护等方面的技术知识。通过实例的讲解和实操操作,学员将会掌握Oracle数据库的应用场景和技术方法,同时还能够结合实际场景进行业务分析、数据建模和优化设计等相关工作。 Oracle案例培训的受益者主要为企业IT技术主管、数据库管理员、软件开发人员等相关从业人员,尤其对于那些在大型企业中有着完善IT体系的企业,Oracle案例培训更是必不可少的培训内容。Oracle案例培训所采用的教学方式丰富多样,例如讲解案例分析、问题归纳和解决、操作演示等多种方式,因此可以更加符合学员的学习需求。 总之,Oracle案例培训是一种有效提高数据库从业人员能力的培训方式,在职场中应用广泛。通过Oracle案例培训的学习,对于企业的业务及数据处理能力的提升将会有很大的帮助。 ### 回答2: Oracle案例培训是一种基于实战案例的培训方法,目的是让学员在学习Oracle数据库的同时,了解业务实战应用中的具体场景和解决方案。Oracle案例培训的特点是实用性强,能够有效提高学员的实际操作水平和解决问题的能力。 在Oracle案例培训中,学员需要通过实际场景的模拟,进行实践操作和解决问题。这种培训方式能够让学员掌握较高的专业技能和实际应用能力。而且通过不同场景的案例学习,能够帮助学员更好地理解Oracle数据库的各种操作和应用方式,提高数据库的管理水平和安全性。 相比于传统的理论学习方式,Oracle案例培训更具体、更实用、更高效。因此,目前在Oracle数据库培训领域中,案例培训已经成为了非常重要的一种培训方式。 总之,Oracle案例培训是一种很好的学习Oracle数据库技能的方式。它可以帮助学员深入了解数据库的实际应用场景,提高操作和解决问题的能力,并为学员未来的职业发展提供更好的保障。

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值