oracle 3个适用sql场景

我们使用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_LOGgroup 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_) rfrom T_ATTENDANCE_LOG t )group by USERNAME_

?

改造结果：

?

?

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

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

?

结果：

?

?