mysql - 我如何在 SQL 中合并多个 WHERE (STUCK)

Question

我如何将这 2 个数据合并在一起以获得类似 this

SELECT A.schlvl AS School, COUNT(DISTINCT A.name) AS NoOfChild ,SUM(B.cat_id=1) AS Picture 
FROM child AS A INNER JOIN
question AS B 
ON A.child_id=B.child_id
WHERE B.answer=0 AND B.cat_id=1
GROUP BY A.schlvl


SELECT COUNT(DISTINCT A.name) AS Gotitright ,SUM(B.cat_id=2) AS Letters
FROM child AS A INNER JOIN
question AS B 
ON A.child_id=B.child_id
WHERE B.answer = 0 AND B.cat_id = 2
GROUP BY A.schlvl

或

类似这样的事情，但我坚持这样做

SELECT A.schlvl AS School, COUNT(DISTINCT A.name WHERE B.answer=0 AND B.cat_id=1) AS Picture , COUNT(DISTINCT A.name WHERE B.answer=0 AND B.cat_id=2) AS Letters
FROM child AS A INNER JOIN
question AS B 
ON A.child_id=B.child_id
GROUP BY A.schlvl

Answer 1

来自您的查询。

你可以尝试在 B.cat_id 中使用 IN 来获取 cat_id 1 或 2 在 WHERE 子句中。

使用聚合函数条件让CASE WHEN在SUM函数中。

SELECT A.schlvl AS School, COUNT(DISTINCT CASE WHEN B.cat_id = 1 THEN A.name END) AS NoOfChild ,SUM(CASE WHEN B.cat_id IN (1,2) THEN 1 END) AS Picture 
FROM 
child AS A INNER JOIN question AS B ON A.child_id=B.child_id
WHERE B.answer=0 AND B.cat_id= in (1,2)
GROUP BY A.schlvl