mysql列转列语句_秒懂mysql语句的行-列转置

1.原图 转为(图形放错位置了，请注意)

数据：

CREATE TABLE stu_score (

grade_id varchar(10) DEFAULT NULL,

subject_name varchar(10) DEFAULT NULL,

max_score int(10) DEFAULT NULL

)

insert into `stu_score`(`grade_id`,`subject_name`,`max_score`) values('1','语文',98);

insert into `stu_score`(`grade_id`,`subject_name`,`max_score`) values('2','数学',95);

insert into `stu_score`(`grade_id`,`subject_name`,`max_score`) values('2','政治',87);

insert into `stu_score`(`grade_id`,`subject_name`,`max_score`) values('5','语文',97);

insert into `stu_score`(`grade_id`,`subject_name`,`max_score`) values('5','数学',100);

insert into `stu_score`(`grade_id`,`subject_name`,`max_score`) values('5','政治',92);

执行语句

1.查询出要求的结果格式

SELECT

CASE grade_id WHEN 1 THEN '一年级'

WHEN 2 THEN '二年级'

WHEN 5 THEN '五年级'

END AS '年级',

CASE subject_name WHEN '语文'THEN max_score END AS '语文',

CASE subject_name WHEN '数学'THEN max_score END AS '数学',

CASE subject_name WHEN '政治'THEN max_score END AS '政治'

FROM stu_score

2,去除null

SELECT

CASE grade_id WHEN 1 THEN '一年级'

WHEN 2 THEN '二年级'

WHEN 5 THEN '五年级'

END AS '年级',

IFNULL(CASE subject_name WHEN '语文'THEN max_score END,0)AS '语文',

IFNULL(CASE subject_name WHEN '数学'THEN max_score END,0) AS '数学',

IFNULL(CASE subject_name WHEN '政治'THEN max_score END,0) AS '政治'

FROM stu_score

3.合并结果。

SELECT

CASE grade_id WHEN 1 THEN '一年级'

WHEN 2 THEN '二年级'

WHEN 5 THEN '五年级'

END AS '年级',

MAX(IFNULL(CASE subject_name WHEN '语文'THEN max_score END,0)) AS '语文',

MAX(IFNULL(CASE subject_name WHEN '数学'THEN max_score END,0)) AS '数学',

MAX(IFNULL(CASE subject_name WHEN '政治'THEN max_score END,0)) AS '政治'

FROM stu_score

GROUP BY grade_id