MySQL ---- SQL语句学习笔记二

SELECT
	name,
	max(
		CASE
		WHEN stage = 'js' THEN
			score
		ELSE
			NULL
		END
	) AS js,
	max(
		CASE
		WHEN stage = 'py' THEN
			score
		ELSE
			NULL
		END
	) AS py,
	max(
		CASE
		WHEN stage = 'sql' THEN
			score
		ELSE
			NULL
		END
	) AS 'sql',
	AVG(score)
	AS ag
FROM
	scores
GROUP BY
	name

SELECT
	s1.`name`,
	s1.score AS js,
	s2.score AS py,
	s3.score AS 'sql',
	(s1.score + s2.score + s3.score) / 3 AS ag
FROM
	scores s1
JOIN scores s2 ON s1.stage = 'js'
AND s2.stage = 'py'
AND s1.`name` = s2.`name`
JOIN scores s3 ON s3.stage = 'sql'
AND s1.`name` = s3.`name`

SELECT
	s1.`name`,
	s1.score AS 'js',
	s2.score AS 'py',
	s3.score AS 'sql',
	s4.ag AS ag
FROM
	scores s1
JOIN scores s2 ON s1.stage = 'js'
AND s2.stage = 'py'
AND s1.`name` = s2.`name`
JOIN scores s3 ON s3.stage = 'sql'
AND s1.`name` = s3.`name`
JOIN (
	SELECT
		`name`,
		AVG(score) AS ag
	FROM
		scores
	GROUP BY
		`name`
) s4 ON s4.`name` = s1.`name`