Mysql 查询求和另一列的条件

Question

这是我的第一个问题，希望不要搞砸了。

这是我制作的一个 SQL Fiddle 来帮助我解释它:http://sqlfiddle.com/#!9/7f2b5c/7

我们有问题、答案、question_types 和 answer_types。

我想知道对于每个问题，有多少人回答了每个 answer_type 以及有多少人回答了该问题，但我无法获得总数。

这是我得到的:

id | fk_question_type | description            | num | value      | total
1  | 1                | How was the breakfast? | 0   | Bad        | 0
1  | 1                | How was the breakfast? | 1   | Good       | 1
1  | 1                | How was the breakfast? | 0   | Indifferent| 0
1  | 1                | How was the breakfast? | 2   | Very good  | 2
2  | 1                | How was the lunch?     | 0   | Bad        | 0
2  | 1                | How was the lunch?     | 1   | Good       | 1
2  | 1                | How was the lunch?     | 0   | Indifferent| 1
2  | 1                | How was the lunch?     | 1   | Very good  | 1

这是我想要的:

id | fk_question_type | description            | num | value      | total
1  | 1                | How was the breakfast? | 0   | Bad        | 3
1  | 1                | How was the breakfast? | 1   | Good       | 3
1  | 1                | How was the breakfast? | 0   | Indifferent| 3
1  | 1                | How was the breakfast? | 2   | Very good  | 3
2  | 1                | How was the lunch?     | 0   | Bad        | 2
2  | 1                | How was the lunch?     | 1   | Good       | 2
2  | 1                | How was the lunch?     | 0   | Indifferent| 2
2  | 1                | How was the lunch?     | 1   | Very good  | 2

我正在尝试的查询:

SELECT id, fk_question_type, description, num, value, SUM(num) AS totalAnswers
FROM (
    (SELECT q.id, q.fk_question_type, q.description, COUNT(a.id) AS num, at.value
    FROM answer a 
    LEFT JOIN question q ON a.`fk_question`=q.`id` 
    LEFT JOIN answer_type at ON at.id = a.`fk_answer_type` 
    GROUP BY q.id, at.id  ORDER BY q.id, at.id)
    UNION ALL
    (SELECT q.id, q.fk_question_type, q.description, 0 AS num, at.value
    FROM answer a 
    LEFT JOIN question q ON a.`fk_question`=q.`id` 
    LEFT JOIN answer_type at ON at.fk_question_type=q.fk_question_type
    GROUP BY q.id, at.id  ORDER BY q.id, at.id)
) AS T 
WHERE fk_question_type = 1
GROUP BY id, value ORDER BY id

如何在保留其他列的同时对具有相同 ID 的行求和？

Answer 1

使用子查询获取每个 id 的总数，并将其与获取每个值行的查询连接起来。

并且在外部查询中，您应该使用SUM(num) 或MAX(num) 来组合两者的num 值UNION 中的查询。

SELECT T.id, fk_question_type, description, SUM(num) AS num, value, T2.totalAnswers
FROM (
    (SELECT q.id, q.fk_question_type, q.description, COUNT(a.id) AS num, at.value
    FROM answer a 
    LEFT JOIN question q ON a.`fk_question`=q.`id` 
    LEFT JOIN answer_type at ON at.id = a.`fk_answer_type` 
    GROUP BY q.id, at.id  ORDER BY q.id, at.id)
    UNION ALL
    (SELECT q.id, q.fk_question_type, q.description, 0 AS num, at.value
    FROM answer a 
    LEFT JOIN question q ON a.`fk_question`=q.`id` 
    LEFT JOIN answer_type at ON at.fk_question_type=q.fk_question_type
    GROUP BY q.id, at.id  ORDER BY q.id, at.id)
) AS T 
JOIN (SELECT q.id, COUNT(*) AS totalAnswers
      FROM answer a 
      LEFT JOIN question q ON a.`fk_question`=q.`id` 
      LEFT JOIN answer_type at ON at.id = a.`fk_answer_type` 
      GROUP BY q.id) AS t2 ON T.id = T2.id
WHERE fk_question_type = 1
GROUP BY T.id, value

DEMO