mysql 列转置_MySQL 行列转置

建立测试表：

DROP TABLE IF EXISTS `my_test_2017_03_29`;

CREATE TABLE `my_test_2017_03_29` (

`id` int(11) NOT NULL,

`event_name` varchar(255) DEFAULT NULL,

`dt` date DEFAULT NULL,

`ev` int(11) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `my_test_2017_03_29` VALUES ('1', 'pc_detail_a', '2017-03-29', '9');

INSERT INTO `my_test_2017_03_29` VALUES ('2', 'pc_detail_b', '2017-03-28', '7');

INSERT INTO `my_test_2017_03_29` VALUES ('3', 'pc_detail_c', '2017-03-27', '4');

INSERT INTO `my_test_2017_03_29` VALUES ('4', 'pc_detail_a', '2017-03-30', '2');

原始数据：

id

event_name

dt

ev

1

pc_detail_a

2017-03-29

9

2

pc_detail_b

2017-03-28

7

3

pc_detail_c

2017-03-27

4

4

pc_detail_a

2017-03-30

2

需要转置为：

pc 点击埋点

2017-03-27

2017-03-28

2017-03-29

2017-03-30

pc_detail_a

0

0

9

2

pc_detail_b

0

7

0

2

pc_detail_c

4

0

0

0

合计

4

7

9

2

即：将 事件名(event_name)作为纵向表头，日期(dt)作为横向表头。

SQL 语句如下：

SET @EE=(

-- 将多行的sum(if)拼接为一行，因为SET @xx要求SELECT结果为一行。

SELECT GROUP_CONCAT(LEFT(dt_stmt, LENGTH(dt_stmt) - 1))

FROM (

-- 拼接上sum(if)

SELECT CONCAT('SUM(IF(dt=\'', dt, '\'', ', ev, 0)) AS \'', dt, '\',') dt_stmt

FROM (

-- 查出所有的日期，升序排列

SELECT DISTINCT dt FROM my_test_2017_03_29 ORDER BY dt ASC

) nil

) nil2

);

SET @QQ=CONCAT(

'SELECT ifnull(event_name,\'合计\') as \'pc 点击埋点\',',

@EE,

' FROM my_test_2017_03_29 GROUP BY event_name WITH ROLLUP'

);

-- 你可以用以下命令查看动态生成的SQL语句：

-- SELECT @EE;

-- SELECT @QQ;

PREPARE stmt2 FROM @QQ;

EXECUTE stmt2;

DEALLOCATE PREPARE stmt2;