SELECT
`Name`,
MAX(
CASE
WHEN Course = 'Chinase' THEN
Score
END
) AS Chinase,
MAX(
CASE
WHEN Course = 'Math' THEN
Score
END
) AS Math,
MAX(
CASE
WHEN Course = 'English' THEN
Score
END
) AS English
FROM
table1
GROUP BY
`Name`
IF实现:
SELECT
`Name`,
SUM(IF (Course = 'Chinase', Score, 0)) AS Chinase,
SUM(IF (Course = 'Math', Score, 0)) AS Math,
SUM(IF (Course = 'English', Score, 0)) AS English
FROM
table1
GROUP BY `Name`
Course不确定,使用动态:
SET @CC='';
SELECT @CC:=CONCAT(@CC,'SUM(IF(Course=\'',Course,'\'',',Score,0)) AS ',Course,',') FROM (SELECT DISTINCT Course FROM table1) A;
SET @HH=CONCAT('SELECT Name,',LEFT(@CC,LENGTH(@CC)-1),' FROM table1 GROUP BY Name');
PREPARE stmt FROM @HH;
EXECUTE stmt;
select name, sum(case when Course='Chinese' then Score end) as Chinese,
sum(case when type='Math' then Score end) as Math,
sum(case when type='English' then Score end) as English
from table1
group by name
题图是Chinase,按这个来。
Course确定情况下:
CASE实现:
IF实现:
Course不确定,使用动态:
你应该缺学习一下原理
百度"mysql行列转换"
只学会这一个sql也没什么用
做数据统计的时候会用到这种神一样的sql,平时phper工作上是用不上的。