SQL行转列,列转行练习

l59725c7b68eee5110221ab809aa58a9b-speerben6168405538415827955-mb7d849a0573641f822833ee11e7206c0.jpg

SELECT sn.name,
       MAX(IF(stu.course = 'mysql', stu.score, null)) AS MYSQL,
       MAX(IF(stu.course = 'JAVA', stu.score, null)) AS JAVA
FROM stdscore sn
INNER JOIN stdscore stu ON sn.name = stu.name
GROUP BY sn.name

CREATE VIEW stuscore AS
SELECT sn.name,
       MAX(IF(stu.course = 'mysql', stu.score, null)) AS MYSQL,
       MAX(IF(stu.course = 'JAVA', stu.score, null)) AS JAVA
FROM stdscore sn
INNER JOIN stdscore stu ON sn.name = stu.name
GROUP BY sn.name;

SELECT * FROM stuscore;
SELECT * FROM stdscore;

SELECT  * FROM (SELECT s.name, 'mysql' as course, s.MYSQL as score FROM stuscore s
UNION ALL
SELECT s2.name, 'JAVA' as course, s2.JAVA as score FROM stuscore s2) as stu
ORDER BY stu.name desc ;