mysql取首条_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代码

--创建考勤记录表

CREATETABLET_ATTENDANCE_LOG

(

ID_VARCHAR(36),

USERNAME_VARCHAR(255),

LOGDATE_VARCHAR(100)

)

--初始化一些测试数据

insertintoT_ATTENDANCE_LOG(ID_,USERNAME_,LOGDATE_)VALUES('1','张三','2014-02-01');

insertintoT_ATTENDANCE_LOG(ID_,USERNAME_,LOGDATE_)VALUES('2','张三','2014-02-02');

insertintoT_ATTENDANCE_LOG(ID_,USERNAME_,LOGDATE_)VALUES('3','张三','2014-02-03');

insertintoT_ATTENDANCE_LOG(ID_,USERNAME_,LOGDATE_)VALUES('4','张三','2014-02-04');

insertintoT_ATTENDANCE_LOG(ID_,USERNAME_,LOGDATE_)VALUES('5','张三','2014-02-05');

insertintoT_ATTENDANCE_LOG(ID_,USERNAME_,LOGDATE_)VALUES('6','张三','2014-02-06');

insertintoT_ATTENDANCE_LOG(ID_,USERNAME_,LOGDATE_)VALUES('11','李四','2014-03-01');

insertintoT_ATTENDANCE_LOG(ID_,USERNAME_,LOGDATE_)VALUES('12','李四','2014-04-01');

insertintoT_ATTENDANCE_LOG(ID_,USERNAME_,LOGDATE_)VALUES('13','李四','2014-05-01');

insertintoT_ATTENDANCE_LOG(ID_,USERNAME_,LOGDATE_)VALUES('21','王五','2014-02-15');

insertintoT_ATTENDANCE_LOG(ID_,USERNAME_,LOGDATE_)VALUES('22','王五','2014-03-15');

--查询

SELECTT.*,T.ROWIDFROMT_ATTENDANCE_LOGT;

结果：

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

Sql代码

withsql1as

(

selectUSERNAME_,substr(LOGDATE_,0,7)asa,count(LOGDATE_)asbfromT_ATTENDANCE_LOG

groupbyUSERNAME_,substr(LOGDATE_,0,7)

)

selectUSERNAME_,

sum(caseAwhen'2014-01'thenBend)一月,

sum(caseAwhen'2014-02'thenBend)二月,

sum(caseAwhen'2014-03'thenBend)三月,

sum(caseAwhen'2014-04'thenBend)四月,

sum(caseAwhen'2014-05'thenBend)五月,

sum(caseAwhen'2014-06'thenBend)六月,

sum(caseAwhen'2014-07'thenBend)七月,

sum(caseAwhen'2014-08'thenBend)八月,

sum(caseAwhen'2014-09'thenBend)九月,

sum(caseAwhen'2014-10'thenBend)十月,

sum(caseAwhen'2014-11'thenBend)十一月,

sum(caseAwhen'2014-12'thenBend)十二月

fromsql1groupbyUSERNAME_

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

结果：

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

Sql代码

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

结果：

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

Sql代码

selectUSERNAME_as员工,max(r)as出勤记录from(

selectUSERNAME_,wmsys.wm_concat(LOGDATE_)OVER(PARTITIONBYUSERNAME_ORDERBYLOGDATE_)r

fromT_ATTENDANCE_LOGt

)groupbyUSERNAME_

改造结果：

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

Sql代码

SELECT*FROM

(

--分组排序加序号

selectUSERNAME_,LOGDATE_,ROW_NUMBER()OVER(PARTITIONBYUSERNAME_ORDERBYLOGDATE_)r

fromT_ATTENDANCE_LOGt

groupbyUSERNAME_,LOGDATE_

)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)

分享到：

2014-05-19 10:14

浏览 361

评论