数据统计查询

如有这么个需求：统计所有班级的各科目的及格人数以及不及格人数，并且以列表列出：

具体实现：

SELECT
	t.banji,
	sum(t.yuwenbujige) yuwenbujige,
	sum(t.shuxuebujige) shuxuebujige,
	sum(t.yuwenjige) yuwenjige,
	sum(t.shuxuejige) shuxuejige
FROM
	(
		SELECT
			banji,
			COUNT(*) yuwenbujige,
			0 shuxuebujige,
			0 yuwenjige,
			0 shuxuejige
		FROM
			xxx
		WHERE
			kumu = '语文'
		AND score < 60
		GROUP BY
			banji
		UNION ALL
			(
				SELECT
					banji,
					0 yuwenbujige,
					COUNT(*) shuxuebujige,
					0 yuwenjige,
					0 shuxuejige
				FROM
					xxx
				WHERE
					kumu = '数学'
				AND score < 60
				GROUP BY
					banji
			)
		UNION ALL
			(
				SELECT
					banji,
					0 yuwenbujige,
					0 shuxuebujige,
					COUNT(*) yuwenjige,
					0 shuxuejige
				FROM
					xxx
				WHERE
					kumu = '语文'
				AND score > 60
				GROUP BY
					banji
			)
		UNION ALL
			(
				SELECT
					banji,
					0 yuwenbujige,
					0 shuxuebujige,
					0 yuwenjige,
					COUNT(*) shuxuejige
				FROM
					xxx
				WHERE
					kumu = '数学'
				AND score > 60
				GROUP BY
					banji
			)
	) t
GROUP BY
	t.banji

查询结果：