MySQL: JOIN (A, B) with SUM B，但在某些情况下 B 不存在行

Question

Hello everyone, I'm a stackoverflow novice.

If I have any wrong about asking this question, hope to redress me. :)

我想做一个提问系统，我在MySQL中有两个表。

1. 表'question':存储问题信息。

2. 表 'question_communication' :它存储管理员和用户之间的问题的回复。

---

这是详细信息表。

question (Table)
    - question_id(INT)
    - uid(INT)
    - category(CHAR)
    - description(CHAR)
    - submit_time(DATETIME)

和

question_communication (Table)
    - question_reply_id(INT)
    - question_id(INT)
    - uid(ID)
    - reply(CHAR)
    - time(DATETIME)
    - seen(TINYINT)           --- Other side has seen the message or not.(0 is not seen, 1 is seen)

---

我希望查询结果包括:

question_id, uid, category, description, submit_time, seen(=1), seen(=0)

然后我试着写下面的代码，

SELECT T1.question_id, T1.uid, T1.category, T1.description, DATE_FORMAT(T1.submit_time, '%Y/%m/%d %H:%i') submit_time, SUM(T2.seen = 1) seen, SUM(T2.seen = 0) notseen
  FROM question T1, question_communication T2
  WHERE T1.question_id = T2.question_id
    AND T2.uid != (Here is attribute.)
  ORDER BY submit_time DESC

但是 WHERE T1.question_id = T2.question_id 这行在一种情况下可能不起作用。

当T1 (question)有问题时，

它在T2 (question_communication) 中没有任何回复。

所以 T1.question_id = T2.question_id 将导致 SQL JOIN 出乎我的意料。

---

我的问题总结:

1. 如何查询成功结果如下:

question_id, uid, category, description, submit_time, seen(=1), seen(=0)

1. 如果在 T1 中有问题，但在 T2 中没有回复。 seen(=1) 和 seen(=0) 必须为零。

---

谢谢大家:)

---

@Gordon Linoff 的回答，我添加了 COALESCE() :

SELECT T1.question_id, T1.uid, T1.category, T1.description,
       DATE_FORMAT(T1.submit_time, '%Y/%m/%d %H:%i') as submit_time,
       COALESCE(SUM(T2.seen = 1), 0) as seen, COALESCE(SUM(T2.seen = 0), 0) as notseen
FROM question T1 LEFT JOIN
     question_communication T2
     ON T1.question_id = T2.question_id 
GROUP BY T1.question_id, T1.uid, T1.category, T1.description,
         DATE_FORMAT(T1.submit_time, '%Y/%m/%d %H:%i') 
ORDER BY submit_time DESC;

Answer 1

我认为您只需要一个左连接。事实上，您应该始终使用明确的 join 语法。您的查询还需要一个 group by。因此，查询看起来像这样:

SELECT T1.question_id, T1.uid, T1.category, T1.description,
       DATE_FORMAT(T1.submit_time, '%Y/%m/%d %H:%i') as submit_time,
       SUM(T2.seen = 1) as seen, SUM(T2.seen = 0) as notseen
FROM question T1 LEFT JOIN
     question_communication T2
     ON T1.question_id = T2.question_id 
GROUP BY T1.question_id, T1.uid, T1.category, T1.description,
         DATE_FORMAT(T1.submit_time, '%Y/%m/%d %H:%i') 
ORDER BY submit_time DESC;

我不知道 T2.uid != (这是属性。) 应该做什么。如果你有一个关于T2的过滤条件，那么把它放在ON子句中。