mysql - 2 带 WHERE 子句的 LEFT JOIN 不起作用

Question

我正在尝试使用 2 个连接进行查询，这两个连接都需要一个 WHERE 子句。我注意到这不起作用，我只能在连接末尾添加一个 WHERE。所以我想知道我怎样才能做到最好..

这是我当前的查询，它不接受第一个连接中的 WHERE:

SELECT  
    p.id, p.username, p.date, p.subject, p.year, p.brand, 
    p.model, p.type, p.bodywork, p.text, p.image, p.chassis, COUNT(c.id) AS answers
FROM 
    posts_tbl AS p
LEFT JOIN 
    post_reply_tbl AS c ON c.post_id = p.id WHERE c.type = 1 AND c.type = 3
LEFT JOIN 
    post_reply_tbl AS a ON a.post_id = p.id WHERE a.type = 2
GROUP BY p.id

Answer 1

将其更改为 AND 而不是 WHERE:

SELECT  p.id, p.username, p.date, p.subject, p.year, p.brand, 
    p.model, p.type, p.bodywork, p.text, p.image, p.chassis, COUNT(c.id) AS answers
FROM posts_tbl AS p
LEFT JOIN post_reply_tbl AS c 
    ON c.post_id = p.id 
    AND c.type = 1 AND c.type = 3
LEFT JOIN post_reply_tbl AS a 
    ON a.post_id = p.id 
    AND a.type = 2
GROUP BY p.id

我认为你需要稍微改变一下，因为 c.type 不能同时有两个值:

SELECT  p.id, p.username, p.date, p.subject, p.year, p.brand, 
    p.model, p.type, p.bodywork, p.text, p.image, p.chassis, COUNT(c.id) AS answers
FROM posts_tbl AS p
LEFT JOIN post_reply_tbl AS c 
    ON c.post_id = p.id 
    AND c.type IN (1, 3)  -- changed to use an OR
LEFT JOIN post_reply_tbl AS a 
    ON a.post_id = p.id 
    AND a.type = 2
GROUP BY p.id

如果您希望 c.type 等于 1 和 3，那么您可能需要考虑使用:

SELECT  p.id, p.username, p.date, p.subject, p.year, p.brand, 
    p.model, p.type, p.bodywork, p.text, p.image, p.chassis, c.answers
FROM posts_tbl AS p
LEFT JOIN
(
    select COUNT(c.id) answers, c.post_id
    from post_reply_tbl c
    where c.type in (1, 3)
    group by c.post_id
    having COUNT(distinct type) = 2
) AS c 
    ON c.post_id = p.id 
LEFT JOIN post_reply_tbl AS a 
    ON a.post_id = p.id 
    AND a.type = 2

只是对您的查询的后续说明，因为您只在一列上使用 GROUP BY，MySQL 决定返回其他列的值，并且这些值可能是意外的。您可以确定返回值的唯一方法是按它们聚合每个列或组。 (参见 MySQL Extensions to GROUP BY)

来自 MySQL 文档:

MySQL extends the use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause. ... You can use this feature to get better performance by avoiding unnecessary column sorting and grouping. However, this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group. The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate. Furthermore, the selection of values from each group cannot be influenced by adding an ORDER BY clause. Sorting of the result set occurs after values have been chosen, and ORDER BY does not affect which values the server chooses.

出于这个原因，最好使用子查询来获取 count()，这样您就可以确保为剩余的列返回正确的结果。

SELECT  p.id, p.username, p.date, p.subject, p.year, p.brand, 
    p.model, p.type, p.bodywork, p.text, p.image, p.chassis, c.answers
FROM posts_tbl AS p
LEFT JOIN
(
    select COUNT(c.id) answers, c.post_id
    from post_reply_tbl c
    where c.type in (1, 3)
) AS c 
    ON c.post_id = p.id 
LEFT JOIN post_reply_tbl AS a 
    ON a.post_id = p.id 
    AND a.type = 2