数据统计查询

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

具体实现:

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

查询结果:



评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值