mysql取首条_SQL报表统计-with as、vm_concat(Oracle)-group_concat(MySQL)

最新推荐文章于 2021-08-21 17:14:38 发布
最新推荐文章于 2021-08-21 17:14:38 发布
阅读量157 收藏
点赞数
文章标签： mysql取首条
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_39613561/article/details/113947595
版权

ORACLE

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

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

1.竖列转横列

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

3.分组排序取首条记录

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

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

业务需求：(对应上面的三个场景)

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

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

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

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

Sql代码 2becf47921da0fca47eec0a6d8107ac4.png

--创建考勤记录表

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;

结果：

c1b7759ab50f2589524c6d8741cb75f5.png

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

Sql代码 2becf47921da0fca47eec0a6d8107ac4.png

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。

结果：

33cb659597d2a8e7915acbdd1fdd33a4.png

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

Sql代码 2becf47921da0fca47eec0a6d8107ac4.png

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

结果：

96a567542af45177d540269d549a00c1.png

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

Sql代码 2becf47921da0fca47eec0a6d8107ac4.png

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

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

fromT_ATTENDANCE_LOGt

)groupbyUSERNAME_

改造结果：

d06d870bdd644af34451c13c500c3d2a.png

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

Sql代码 2becf47921da0fca47eec0a6d8107ac4.png

SELECT*FROM

(

--分组排序加序号

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

fromT_ATTENDANCE_LOGt

groupbyUSERNAME_,LOGDATE_

)whereR=1

结果：

f2b408af3d189f787476c143e19b8689.png

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)

分享到：

18e900b8666ce6f233d25ec02f95ee59.png

72dd548719f0ace4d5f9bca64e1d7715.png

2014-05-19 10:14

浏览 361

评论

weixin_39613561
关注
oracle的wm_concat()和mysql的group_concat()合并同列变成一行的用法以及和concat()合并不同列的区别
小小鱼儿小小林的博客
07-11 6235
前言 标题几乎已经说的很清楚了，在oracle中，concat()函数和 “ || ” 这个的作用是一样的，是将不同列拼接在一起；那么wm_concat()是将同属于一个组的（group by）同一个字段拼接在一起变成一行。mysql是一样的，只不过mysql用的是group_concat()这个函数，用法是一样的，这里就不过多介绍了。大家可以直接戳这篇文章 了解浅析MySQL中concat以...
mysql和oracle根据某一字段分组合并并拼接其中字段 GROUP_CONCAT和LISTAGG
weixin_57498976的博客
09-17 2865
mysql和oracle根据某一字段分组合并并拼接其中字段 GROUP_CONCAT和LISTAGG
mysql 类似wm concat_oracle的wm_concat()和mysql的group_concat()合并同列变成一行的用法以及和concat()合并不同列的区别...
weixin_36278346的博客
02-18 3868
前言标题几乎已经说的很清楚了，在oracle中，concat()函数和 “ || ” 这个的作用是一样的，是将不同列拼接在一起；那么wm_concat()是将同属于一个组的(group by)同一个字段拼接在一起变成一行。mysql是一样的，只不过mysql用的是group_concat()这个函数，用法是一样的，这里就不过多介绍了。大家可以直接戳这篇文章 了解浅析MySQL中concat以及g...
SQL报表统计-with as、vm_concat(Oracle)-group_concat(MySQL)
modabobo
05-19 270
ORACLE 以下转自：http://snkcxy.iteye.com/blog/2067814 我们使用oracle做一些统计的时候，时常碰到如下场景： 1.竖列转横列 2.分组并合并某列作为结果集 3.分组排序取首条记录 我们使用一个简化的业务场景，来展示这三个场景如何使用sql来解决。 业务场景：一张表记录着员工的出勤记录 业务需求：（对应上面的三个场景） 1.统计员工...
mysql的wm_concat函数_oracle中的wm_concat 对应 MySQL 的 GROUP_CONCAT
weixin_42575505的博客
02-02 2573
MySQL 的 GROUP_CONCAT见：http://www..com/appleat/archive/2012/09/03/2669033.htmloracle中的wm_concat见：https://blog..net/njdxtj/article/details/78625571MySQL 部分：一、CONCAT()函数CONCAT()函数用于将多个字符串连接成一个字符串。使用数据表I...
mysql之wm_concat
有教无类的专栏
10-11 7761
SET @enames=''; SET @last_deptno=-1; SELECT deptno, MAX(enames) AS enames FROM (SELECT deptno, IF(@last_deptno = deptno, @rn := @rn + 1, @rn := 1) AS rn, IF(@last_
mysql wm concat_解决 wm_concat函数 长度不够问题
weixin_32011209的博客
02-01 313
自定义函数如下：CREATE OR REPLACE TYPE "T_LINK_LOB"ASOBJECT(V_LOB CLOB,STATIC FUNCTION ODCIAGGREGATEINITIALIZE (SCTX IN OUT NOCOPY T_LINK_LOB)RETURN NUMBER,MEMBER FUNCTION ODCIAGGREGATEITERATE (SELF IN OUT NO...
shell查mysql_通过shell检查mysql主机和数据库，生成html报表的脚本
weixin_32148341的博客
01-19 369
该脚本主要用于大致诊断MYSQL主机和数据库配置及性能收集，脚本部分功能展示如下：实现该上述展示功能的shell脚本如下：file_output='os_mysql_summary.html'td_str=''th_str=''myuser="root"mypasswd="password"myip="192.168.11.101"myport="3307"mysql_cmd="mysql-u$...
Oracle数据库和Mysql数据库的sql语句执行顺序
qq_41568604的博客
08-21 647
MYSQL数据库中sql的书写顺序： SELECT [DISTINCT] FROM JOIN ON WHERE GROUP BY HAVING UNION ORDER BY LIMIT MYSQL数据库中sql的执行顺序： FROM ON JOIN WHERE GROUP BY --执行后（有聚合函数），其结果一定是唯一的，也不需要针对这个字段使用distinct HAVING SELECT DISTINCT -- 要写在所有要查询字段的前面，后面有几个字段，就代表修饰几个字段，而不是紧随di
mysql 字符串连接 concat、gropu_concat用法
kevin@hbstu.net
05-12 1354
select group_concat(id) from streat_temp where id not in (45383,19979); 输出结果： 28372,28373,28385,28370,28367,28395,28386,28378,28368,28387,28371,28369,28380,28394,28379,28388,28392,28377,28404,28384,2
mysql group_concat()函数用法总结
12-16
本文实例讲述了mysql group_concat()函数用法。分享给大家供大家参考，具体如下： group_concat()，手册上说明:该函数返回带有来自一个组的连接的非NULL值的字符串结果。比较抽象，难以理解。 通俗点理解，其实是这样的：group_concat()会计算哪些行属于同一组，将属于同一组的列显示出来。要返回哪些列，由函数参数(就是字段名)决定。分组必须有个标准，就是根据group by指定的列进行分组。 group_concat函数应该是在内部执行了group by语句，这是我的猜测。 1.测试语句： SELECT group_concat(town) FROM `pl
mysql sql with as_oracle高级实用sql(with as)
weixin_28235889的博客
02-01 352
with as 使用with as使用在当需要提供多个字段数据时，避免重复性的使用union all或者使用decade函数加标签等方法，with as可以将多个字段的结果集分开查询作为一个结果集(即是看做一张新的表)，之后再对该表操作，降低sql复杂度，也降低使用union all等可能带来的失误率，with as 与join结合可一次性得到你想要的多字段数据。简单例子如下(可执行查看)：--w...
Oracle数据库迁移MySQL——wm_concat()方法
println小强的博客
05-16 2869
将Oracle数据库迁移至MySQL时，使用MySQL的GROUP_CONCAT()替换Oracle的wm_concat()方法，由于MySQL的GROUP_CONCAT()默认长度限制为1024，并不能满足项目需要，修改MySQL的my.ini将长度限制调整为102400 group_concat_max_len = 102400 重启MySQL生效;...
Slq怎么样获取首条记录和最后一条记录
banqiuxiang4970的博客
06-01 323
sql如何查询表的第一条记录和最后一条记录 方法一：使用top select TOP 1 * from apple;TOP 1 表示表apple中的第一条数据 select TOP 1 * from apple order by id desc;TOP也是第一条数据，但是order by（以什么排序），id desc 以id的降序排列，所以要是id要是增长的话，最后一条数据就被...
关于Oracle函数wm_concat的一些问题
工作手记
04-22 4392
Oracle的wm_concat函数在不同的版本中，返回值类型是不同的。因为是Oracle内部函数，不建议在实际应用中使用，该函数随着oracle版本的升级也不会向下兼容。
mysql的wm_concat函数_oracle 函数 WMSYS.WM_CONCAT 的用法 与 mysql 函数GROUP_CONCAT的用法-阿里云开发者社区...
weixin_42515063的博客
02-02 1697
1、oracle函数 WMSYS.WM_CONCAT的使用如图，将图1的结果变成图2的结果，使用函数 WMSYS.WM_CONCAT 即可。查询语句需要配合使用group byselect aa,wmsys.wm_concat(t1.name) from (select t.name,to_char(t.createdate,'yyyy-mm-dd') aa from td_user t wher...
mysql5.6 with as 用法_Oracle sql 语句中with....as的用法
weixin_42396588的博客
02-04 2298
Oracle的SQL语句中，With查询语句不是以select开头，而是以with关键字开头。 with...as语句增加了SQL 的可读性，构造子查询，结构会更加清晰。可以“一次分析，多次使用”，可以提高性能，达到“少读”的目标。 具体使用with....as.....的SQL语句举例：with sql_conc_waits as (select sql_id, substr(sql_text...
mysql分组函数wm_oracle 函数 WMSYS.WM_CONCAT 的用法 与 mysql 函数GROUP_CONCAT的用法
weixin_42151772的博客
02-08 1038
一、CONCAT()函数 CONCAT()函数用于将多个字符串连接成一个字符串。 使用数据表Info作为示例，其中SELECT id,name FROM info LIMIT 1;的返回结果 为 +----+--------+ | id | name| +----+--------+ |1 | BioCyc | +----+--------+ 1、语法及使用特点1、oracle函数 WM...
mysql wm concat sep,MySQL拼接函数CONCAT的使用心得
weixin_36242382的博客
03-17 248
一、concat函数相关的几种用法1-1、函数：concat(str1,str2,…)concat 函数一般用在SELECT 查询语法中，用于修改返回字段内容，例如有张LOL英雄信息表如下mysql> select * from `LOL`;+----+---------------+--------------+-------+| id | hero_title | hero_nam...
MySQL GROUP_CONCAT函数实现字符串拼接
"这篇文章主要介绍了MySQL中的GROUP_CONCAT函数，它是用于将结果集中某一列的非NULL值拼接成一个字符串的聚合函数。在处理数据报告或者数据分析时，这种功能非常实用，尤其当需要将一组数据简洁地表示为一个字符串时...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值