SQL报表统计-with as、vm_concat(Oracle)-group_concat(MySQL)

ORACLE

以下转自：http://snkcxy.iteye.com/blog/2067814

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

1.竖列转横列

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

3.分组排序取首条记录

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

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

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

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

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

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

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

Sql代码

1. --创建考勤记录表
2. CREATETABLET_ATTENDANCE_LOG
3. (
4. ID_VARCHAR(36),
5. USERNAME_VARCHAR(255),
6. LOGDATE_VARCHAR(100)
7. )
10. --初始化一些测试数据
11. insertintoT_ATTENDANCE_LOG(ID_,USERNAME_,LOGDATE_)VALUES('1','张三','2014-02-01');
12. insertintoT_ATTENDANCE_LOG(ID_,USERNAME_,LOGDATE_)VALUES('2','张三','2014-02-02');
13. insertintoT_ATTENDANCE_LOG(ID_,USERNAME_,LOGDATE_)VALUES('3','张三','2014-02-03');
14. insertintoT_ATTENDANCE_LOG(ID_,USERNAME_,LOGDATE_)VALUES('4','张三','2014-02-04');
15. insertintoT_ATTENDANCE_LOG(ID_,USERNAME_,LOGDATE_)VALUES('5','张三','2014-02-05');
16. insertintoT_ATTENDANCE_LOG(ID_,USERNAME_,LOGDATE_)VALUES('6','张三','2014-02-06');
18. insertintoT_ATTENDANCE_LOG(ID_,USERNAME_,LOGDATE_)VALUES('11','李四','2014-03-01');
19. insertintoT_ATTENDANCE_LOG(ID_,USERNAME_,LOGDATE_)VALUES('12','李四','2014-04-01');
20. insertintoT_ATTENDANCE_LOG(ID_,USERNAME_,LOGDATE_)VALUES('13','李四','2014-05-01');
22. insertintoT_ATTENDANCE_LOG(ID_,USERNAME_,LOGDATE_)VALUES('21','王五','2014-02-15');
23. insertintoT_ATTENDANCE_LOG(ID_,USERNAME_,LOGDATE_)VALUES('22','王五','2014-03-15');
25. --查询
26. SELECTT.*,T.ROWIDFROMT_ATTENDANCE_LOGT;

结果：

1.统计员工2014年的每月出勤情况

Sql代码

1. withsql1as
2. (
3. selectUSERNAME_,substr(LOGDATE_,0,7)asa,count(LOGDATE_)asbfromT_ATTENDANCE_LOG
4. groupbyUSERNAME_,substr(LOGDATE_,0,7)
5. )
6. selectUSERNAME_,
7. sum(caseAwhen'2014-01'thenBend)一月,
8. sum(caseAwhen'2014-02'thenBend)二月,
9. sum(caseAwhen'2014-03'thenBend)三月,
10. sum(caseAwhen'2014-04'thenBend)四月,
11. sum(caseAwhen'2014-05'thenBend)五月,
12. sum(caseAwhen'2014-06'thenBend)六月,
13. sum(caseAwhen'2014-07'thenBend)七月,
14. sum(caseAwhen'2014-08'thenBend)八月,
15. sum(caseAwhen'2014-09'thenBend)九月,
16. sum(caseAwhen'2014-10'thenBend)十月,
17. sum(caseAwhen'2014-11'thenBend)十一月,
18. sum(caseAwhen'2014-12'thenBend)十二月
19. fromsql1groupbyUSERNAME_

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

结果：

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

Sql代码

1. selectUSERNAME_as员工,wmsys.wm_concat(LOGDATE_)as出勤记录fromT_ATTENDANCE_LOGtgroupbyUSERNAME_

结果：

但是我们发现这个统计出来的结果是乱序，改造一下

Sql代码

1. selectUSERNAME_as员工,max(r)as出勤记录from(
2. selectUSERNAME_,wmsys.wm_concat(LOGDATE_)OVER(PARTITIONBYUSERNAME_ORDERBYLOGDATE_)r
3. fromT_ATTENDANCE_LOGt
4. )groupbyUSERNAME_

改造结果：

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

Sql代码

1. SELECT*FROM
2. (
3. --分组排序加序号
4. selectUSERNAME_,LOGDATE_,ROW_NUMBER()OVER(PARTITIONBYUSERNAME_ORDERBYLOGDATE_)r
5. fromT_ATTENDANCE_LOGt
6. groupbyUSERNAME_,LOGDATE_
7. )whereR=1

结果：

MYSQL

以下转自：http://www.poluoluo.com/jzxy/200812/53698.html

本文通过实例介绍了MySQL中的group_concat函数的使用方法，比如select group_concat(name) 。
MySQL中group_concat函数
完整的语法如下：
group_concat([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator '分隔符'])

基本查询
mysql> select * from aa;
+------+------+
| id| name |
+------+------+
|1 | 10|
|1 | 20|
|1 | 20|
|2 | 20|
|3 | 200 |
|3 | 500 |
+------+------+
6 rows in set (0.00 sec)

以id分组，把name字段的值打印在一行，逗号分隔(默认)
mysql> select id,group_concat(name) from aa group by id;
+------+--------------------+
| id| group_concat(name) |
+------+--------------------+
|1 | 10,20,20|
|2 | 20 |
|3 | 200,500|
+------+--------------------+
3 rows in set (0.00 sec)

以id分组，把name字段的值打印在一行，分号分隔
mysql> select id,group_concat(name separator ';') from aa group by id;
+------+----------------------------------+
| id| group_concat(name separator ';') |
+------+----------------------------------+
|1 | 10;20;20 |
|2 | 20|
|3 | 200;500 |
+------+----------------------------------+
3 rows in set (0.00 sec)

以id分组，把去冗余的name字段的值打印在一行，
逗号分隔

mysql> select id,group_concat(distinct name) from aa group by id;
+------+-----------------------------+
| id| group_concat(distinct name) |
+------+-----------------------------+
|1 | 10,20|
|2 | 20 |
|3 | 200,500 |
+------+-----------------------------+
3 rows in set (0.00 sec)

以id分组，把name字段的值打印在一行，逗号分隔，以name排倒序
mysql> select id,group_concat(name order by name desc) from aa group by id;
+------+---------------------------------------+
| id| group_concat(name order by name desc) |
+------+---------------------------------------+
|1 | 20,20,10 |
|2 | 20|
|3 | 500,200|
+------+---------------------------------------+
3 rows in set (0.00 sec)