我们使用oracle做一些统计的时候,时常碰到如下场景:
1.竖列转横列
2.分组并合并某列作为结果集
3.分组排序取首条记录
我们使用一个简化的业务场景,来展示这三个场景如何使用sql来解决。
业务场景:一张表记录着员工的出勤记录
业务需求:(对应上面的三个场景)
1.统计员工某年的每月出勤记录数
2.查询每个人的出勤记录
3.获得每个员工第一天上班的出勤记录
首先我们先创建测试数据表和测试数据
--创建考勤记录表 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年的每月出勤情况
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.查询每个人的出勤记录
select USERNAME_ as 员工,wmsys.wm_concat(LOGDATE_) as 出勤记录 from T_ATTENDANCE_LOG t 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.获得每个员工第一天上班的出勤记录
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
结果:
相关推荐
oracle的按月统计sql..............................
oracle SQL查询工具oracle SQL查询工具oracle SQL查询工具oracle SQL查询工具oracle SQL查询工具oracle SQL查询工具oracle SQL查询工具oracle SQL查询工具oracle SQL查询工具oracle SQL查询工具oracle SQL查询工具...
Oracle与SQLServer的SQL语法差异,用简单易懂的语言和实例对Oracle和Sql Server语法之间的差异进行了对比分析,更加适用于入门的人
实用Oracle SQL&PLSQL.
Oracle Sql语句转换成Mysql Sql语句java 源码,非常简单,只要给定源oracle sql语句地址,和生成目标文件地址运行即可。
Oracle批量执行传多个参数多个SQL文件,适合于跑批,生成环境直接测试,没有问题
oracle sql and pl/sql
Oracle SQL 内置函数大全 SQL中的单记录函数 给出整数,返回对应的字符 连接两个字符串 增加或减去月份 用于对查询到的结果进行排序输出
解决oracle sqldeveloper无法连接mysql、SQLServer问题,sqlDeveloper是ORACLE数据库开发工具,自带的是无法连接MS SQL Server以及mysql的,想连接的话需要第三方工具。 使用方法: 解压出来后将2个jar放入jlib...
对ORACLE-SQL进行一些布局优化,更新它的格式
Oracle SQL 11G2 官方文档,包含4份,分别是PLSQL语言参考、PLSQL程序包与类型、Oracle SQL参考、SQL快速参考
sqlserver自动生成sql语句工具sqlserver转oracle
这个版本可以在Win11上直接运行无需安装.NETFRAMEWORK3.5
oracle消耗资源的sql查询语句记录
Oracle数据库各类指标统计的SQL语句
查询ORACLE 系统中当前会话正在执行的有关SQL语句。
ORACLE 数据库 SQL ORACLE 数据库 SQL ORACLE 数据库 SQL ORACLE 数据库 SQL ORACLE 数据库 SQL ORACLE 数据库 SQL
解决的办法其实很简单,在注册表HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE这个位置新建一个字符串值ORACLE_HOME,把oracle的HOME值写进去就可以了!在10g以上的版本中一般ORACLE_HOME都存在HKEY_LOCAL_MACHINE\SOFTWARE\...
oracle数据库的性能优化直接关系到系统的运行效率,而影响数据库性能的一个重要因素就是sql性能问题。本书是作者十年磨一剑的成果之一,深入分析与解剖oracle sql优化与调优技术,主要内容包括: 第一篇“执行计划...
本技术专题主要介绍如何使用Oracle SQL Developer和其他开发工具,内容包括使用Oracle Database Home Page、在Oracle中使用SQL*Plus、如何用SQL Developer来操作Oracle数据库以及表列定义等等。