`

SQL进行条件统计:GROUP_CONCAT 行转列

    博客分类:
  • DB
阅读更多

 

 (编写不易,转载请注明: https://shihlei.iteye.com/blog/2442053)

一 概述

    继续支持运营出统计数据,需要行转列,看来下 GROUP_CONCAT 函数,做个记录。

 

二 场景

1) 表结构

员工表employee 

id, name , type , age, leader

 

CREATE TABLE `employee`(
`id` BIGINT NOT NULL AUTO_INCREMENT COMMENT 'id',
`name` VARCHAR(100) NOT NULL COMMENT '姓名',
`type` VARCHAR(2) NOT NULL COMMENT '类别 A,B,C',
`age` INT(2) NOT NULL COMMENT '年龄',
`leader` VARCHAR(100) NOT NULL COMMENT '组长',
  PRIMARY KEY (`id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='员工表';

insert into `employee`(`name`,`type`,`age`,`leader`) values 
('name_10','A',10,'tom'), 
('name_45','A',45,'tom'), 
('name_47','A',47,'mike'), 
('name_60','A',60,'tom'), 
('name_12','A',12,'tom'), 
('name_70','A',70,'tom'), 
('name_10','B',10,'mike'), 
('name_50','B',50,'tom'), 
('name_51','B',51,'tom');

 

 

2)统计需求

查询各个leader 下 年龄大于40岁的各类员工,按如下表头输出,按年龄从小到大

 

表头:leader,A类员工,B类员工

 

3)脚本

SELECT leader, 
GROUP_CONCAT(IF(`type`='A' AND `age` > 40,`name`,NULL) ORDER BY `age`) as 'A类员工',
GROUP_CONCAT(IF(`type`='B' AND `age` > 40,`name`,NULL) ORDER BY `age`) as 'B类员工'
FROM employee 
GROUP BY leader;

 

4) 结果

 

 

 

三 函数说明

1) 定义:

GROUP_CONCAT ( [DISTINCT] col_name1 [ORDER BY clause]  [SEPARATOR str_val] ) 

 

2)说明:

用于行转列,依赖group by分组,可以配合IF函数进行条件拼接

  • Dinstinct: 指定是否排重
  • Order By: 指定排序字段
  • Separator: 指定分隔符,默认逗号

3)关于拼接长度限制: 

GROUP_CONCAT 通过 group_concat_max_len 变量 设置了 拼接的长度限制,超过最大显示会被截断,可以通过修改该变量保证不被截断

 

查看长度限制 :show variables like 'group_concat_max_len';

 

修改:

SET GLOBAL group_concat_max_len = 102400;

SET SESSION group_concat_max_len = 102400; 

  

或者 设置 group_concat_max_len = -1 指定最大

 

 

  • 大小: 53.8 KB
  • 大小: 36.1 KB
分享到:
评论

相关推荐

    【GROUP_CONCAT】使用之MySQL官方文档翻译

    ### GROUP_CONCAT 函数详解 #### 一、概述 `GROUP_CONCAT` 是 MySQL 中一个非常实用的聚合函数,主要用于将来自同一分组的多个值连接成一个字符串。这一功能在许多场景下都非常有用,例如汇总数据、创建列表等。 ...

    ORA-00904: "WM_CONCAT": 标识符无效

    例如,如果你原来的语句是`SELECT WM_CONCAT(column) FROM table`,你可以改为`SELECT LISTAGG(column, ', ') WITHIN GROUP (ORDER BY column) FROM table`。这样,LISTAGG会按照column的顺序将行连接成一个字符串,...

    MySQL GROUP_CONCAT限制解决方案

     GROUP_CONCAT函数可以拼接某个字段值成字符串,默认的分隔符是 逗号,即”,” ,  如果需要自定义分隔符可以使用 SEPARATOR  如: SELECT GROUP_CONCAT(name SEPARATOR '_') FROM user 限制:  GROUP_CONCAT...

    SqlServer GroupConcat

    而`SqlServer GroupConcat`的CLR实现则提供了一个更为简洁的解决方案,使得在SQL Server中进行字符串聚合更加方便。 文件列表中,我们看到有`.html`、`.json`等文件,这可能包含了项目文档、问题讨论、许可信息以及...

    MySQL统计函数GROUP_CONCAT使用陷阱分析

    MySQL中的GROUP_CONCAT函数是一个非常实用的统计函数,它允许我们合并特定列的值,并以指定的分隔符连接它们。然而,在某些情况下,如果不注意,可能会遇到一些使用陷阱,导致数据被截断或者出现警告。本文将深入...

    如何修改Mysql中group_concat的长度限制

    在MySQL数据库中,`GROUP_CONCAT` 函数是一个非常实用的工具,它允许你在聚合查询中将多个行的某个列值合并成一个单一的字符串,每个值之间由默认的逗号分隔。然而,当你处理大量数据时,可能会遇到一个限制,即`...

    ORA-00904 WMSYS.WM_CONCAT标识符无效解决方案

    例如,`LISTAGG(column, delimiter) WITHIN GROUP (ORDER BY column)` 将按指定列排序并以指定分隔符连接所有行的值。 2. **自定义函数**:如果不能使用LISTAGG(例如,在Oracle 10g或更低版本中),可以创建自己的...

    wm_concat函数所需资源包

    在Oracle数据库环境中,`WM_CONCAT`函数是一个非常实用的工具,用于将多个行的数据合并成单个字符串。然而,自Oracle 11g R2版本开始,这个函数被标记为弃用,导致许多用户在使用时遇到问题。标题中的"wm_concat函数...

    wm_concat函数DDL.zip

    在Oracle数据库中,`wm_concat`函数曾是一个非常实用的工具,用于将多个行的数据合并成单个字符串,尤其在需要进行数据汇总时非常方便。然而,从Oracle 11g版本开始,出于性能和安全性的考虑,Oracle官方取消了这个...

    MySQL中group_concat函数深入理解

    MySQL中的`GROUP_CONCAT`函数是一个非常实用的聚合函数,它允许你在分组查询中将一组行的某个列值合并成一个字符串,每个值之间由指定的分隔符隔开。这个函数对于数据汇总和报告生成特别有用,因为它可以把多行数据...

    Mysql的GROUP_CONCAT()函数使用方法

    在本文中,我们将深入探讨GROUP_CONCAT()的用法,并通过实例进行讲解。 GROUP_CONCAT()函数的基本语法如下: ```sql GROUP_CONCAT([DISTINCT] expr[, expr ...] [ORDER BY {unsigned_integer | col_name | expr} ...

    解决Oracle没有WM_CONCAT函数.zip

    在Oracle数据库中,WM_CONCAT是一个非常实用的聚合函数,用于将一组字符串连接成一个单一的字符串,类似于SQL Server中的STRING_AGG或MySQL中的GROUP_CONCAT。然而,Oracle官方并没有提供这个函数,它是一个第三方...

    BLOG_Oracle_wm_concat包的订制.pdf

    在Oracle数据库中,`WM_CONCAT`函数曾经是一个非常实用的工具,尤其在处理行转列的问题时。然而,这个函数在Oracle 12c及更高版本中被废弃,因为其存在一些不稳定性和性能问题。在10G和11GR2版本中,`WM_CONCAT`有时...

    mysql中GROUP_CONCAT的使用方法实例分析

    在MySQL中,`GROUP_CONCAT` 是一个非常实用的聚合函数,它允许你在分组查询时将同一组内的多个值合并成一个字符串。这个函数在处理列表或者集合数据时非常有用,尤其在需要将关联表中的多行数据整合成一行展示时。...

    mysql的group_concat函数使用示例

    如果我们想要根据`id`字段对数据进行分组,并将每个`id`对应的`name`字段的值合并为一行,可以使用以下SQL语句: ```sql SELECT id, GROUP_CONCAT(name) FROM aa GROUP BY id; ``` 执行后,结果会如下所示: ```...

    mysql group_concat()函数用法总结

    要注意的是,`GROUP_CONCAT()`函数只能用于单个字段或表达式,如果你尝试将多个列作为参数,如`GROUP_CONCAT(name, sex)`,结果将是组合后的字符串,每个字段的值之间由逗号分隔,而不是每行记录的值。在某些场景下...

    wmsys.wm_concat详细示例.txt

    SELECT id, wmsys.wm_concat(name) AS name FROM idtable GROUP BY id; ``` 这段代码的结果将返回每个`id`对应的`name`列值的汇总字符串,如`10`对应的`name`列值为`'ab,bc,cd'`,而`20`对应的`name`列值为`'hi,ij,...

Global site tag (gtag.js) - Google Analytics