1.准备工作
- 表创建
CREATE TABLE `column_row` (
`school_code` varchar(255) DEFAULT NULL,
`subject_type` int(255) DEFAULT NULL,
`finish_num` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
表结构:
- 插入数据
INSERT INTO `column_row`(`school_code`, `subject_type`, `finish_num`) VALUES ('10001', 1, 25);
INSERT INTO `column_row`(`school_code`, `subject_type`, `finish_num`) VALUES ('10002', 2, 30);
INSERT INTO `column_row`(`school_code`, `subject_type`, `finish_num`) VALUES ('10001', 2, 41);
结果:
2.进入正题
SELECT
school_code,
subject_type,
-- SEPARATOR ';'分隔符 默认是逗号
GROUP_CONCAT(finish_num SEPARATOR ';') new_finish_num
FROM
column_row
GROUP BY
school_code
结果:
SELECT
school_code,
subject_type,
MAX( CASE subject_type WHEN 1 THEN finish_num ELSE NULL END ) AS new_finish_num1,
MAX( CASE subject_type WHEN 2 THEN finish_num ELSE NULL END ) AS new_finish_num2
FROM
column_row
GROUP BY
school_code