mysql列边行_mysql-行转列、列转行

group_concat()，函数说明

手册上说明:该函数返回带有来自一个组的连接的非NULL值的字符串结果；

通俗点理解，其实是这样的：group_concat()会计算哪些行属于同一组，将属于同一组的列显示出来。要返回哪些列，由函数参数(就是字段名)决定。分组必须有个标准，就是根据group by指定的列进行分组。

1、行转列

-- 不转sql语句

SELECT t2.HIERARCHY_ID FROM t_hierarchy t2 WHERE t2.`DEPARTMENT_PID` = (SELECT t1.DEPARTMENT_ID FROM t_staff t1 WHERE t1.id = 3) AND t2.`TYPE`=1030 AND STATUS=1

-- 将行转列以","隔开

SELECT GROUP_CONCAT(t2.HIERARCHY_ID SEPARATOR ',') FROM t_hierarchy t2 WHERE t2.`DEPARTMENT_PID` = (SELECT t1.DEPARTMENT_ID FROM t_staff t1 WHERE t1.id = 3) AND t2.`TYPE`=1030 AND STATUS=1;

-- 将行转列以","隔开,如果填上其他符号则是以","+符号隔开

SELECT GROUP_CONCAT(t2.HIERARCHY_ID,'') FROM t_hierarchy t2 WHERE t2.`DEPARTMENT_PID` = (SELECT t1.DEPARTMENT_ID FROM t_staff t1 WHERE t1.id = 3) AND t2.`TYPE`=1030 AND STATUS=1;

-- 将行转列以","隔开

SELECT GROUP_CONCAT(t2.HIERARCHY_ID) FROM t_hierarchy t2 WHERE t2.`DEPARTMENT_PID` = (SELECT t1.DEPARTMENT_ID FROM t_staff t1 WHERE t1.id = 3) AND t2.`TYPE`=1030 AND STATUS=1;

(不转输出)

(行转列后输出)

2、行转列列传行更具体例子

1、查看数据

SELECT * FROM tabName ;

2、列转行统计数据

SELECT DATE ,

MAX(CASE NAME WHEN '小说' THEN Scount ELSE 0 END ) 小说,

MAX(CASE NAME WHEN '微信' THEN Scount ELSE 0 END ) 微信

FROM TabName

GROUP BY DATE