大家好,我是一个堆栈溢出新手。
如果我问这个问题有什么错误,希望能纠正我。 :)
我想做一个提问系统,我在MySQL中有两个表。
-
表"问题":它存储问题信息。
-
表'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
超出我的预期。
我的问题摘要:
- 如何成功查询结果,如下所示:
question_id, uid, category, description, submit_time, seen(=1), seen(=0)
- 如果 T1 中有问题,但 T2 中没有回复。
seen(=1)
和seen(=0)
必须zero
.
谢谢大家:)
来自
@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;
我认为你只需要一个left join
. 实际上,您应该始终使用显式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 != (Here is attribute.)
应该做什么。 如果您对 T2
有过滤条件,则将其放在 ON
子句中。