如有这么个需求:统计所有班级的各科目的及格人数以及不及格人数,并且以列表列出:
具体实现:
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
查询结果: