SQL行转列

1.SQL行转列

1.1 test表结构

year, month, amout

1.2 test表数据

1.3 查询SQL语句

SELECT
	a.`year`,
	m1,
	m2,
	m3,
	m4
FROM
	(
		(
			SELECT
				`year`,
				amout AS m1
			FROM
				test
			WHERE
				`month` = 1
		) a
		LEFT JOIN (
			SELECT
				`year`,
				amout AS m2
			FROM
				test
			WHERE
				`month` = 2
		) b ON a.`year` = b.`year`
		LEFT JOIN (
			SELECT
				`year`,
				amout AS m3
			FROM
				test
			WHERE
				`month` = 3
		) c ON a.`year` = c.`year`
		LEFT JOIN (
			SELECT
				`year`,
				amout AS m4
			FROM
				test
			WHERE
				`month` = 4
		) d ON a.`year` = d.`year`
	)

1.4 查询结果

2.查询没门课都大于80分的学生

2.1 student表结构

name, course, marks

2.2 student表数据

2.3 查询SQL

SELECT
	b.`name`,
	b.course_nums,
	a.sc_course_nums
FROM
	(
		SELECT
			`name`,
			COUNT(course) AS course_nums
		FROM
			student
		GROUP BY
			`name`
	) b
LEFT JOIN (
	SELECT
		`name` AS sc_name,
		COUNT(course) AS sc_course_nums
	FROM
		student
	WHERE
		marks >= 80
	GROUP BY
		`name`
) a ON a.sc_name = b.`name`
AND b.course_nums = a.sc_course_nums
WHERE
	sc_course_nums IS NOT NULL

2.4 查询结果