mysql - 在 WHERE 和 ORDER BY 子句中重用 LEFT JOIN 结果的问题

Question

SELECT s.*,
u.username,
u.fullname,
c.title AS ctitle,
c.description AS cdescription,
sa.attention,
sp.popularity,
COUNT(DISTINCT f.id) AS favorites,
COUNT(DISTINCT st.id) AS stars,
COUNT(DISTINCT v.id) AS views
FROM shots s
INNER JOIN users u ON u.id = s.user_id
INNER JOIN categories c ON c.id = s.cat_id
LEFT OUTER JOIN(
    SELECT shot_id, round(AVG(count),2) AS attention
    FROM points 
    WHERE date > DATE_SUB(CURDATE(),INTERVAL 2 DAY)
    GROUP BY shot_id
) sa ON sa.shot_id = s.id
LEFT OUTER JOIN(
    SELECT shot_id, SUM(count) AS popularity
    FROM points 
    GROUP BY shot_id
) sp ON sp.shot_id = s.id
LEFT OUTER JOIN favorites f ON f.shot_id = s.id
LEFT OUTER JOIN stars st ON st.shot_id = s.id
LEFT OUTER JOIN views v ON v.shot_id = s.id
**WHERE s.library = 1 AND sa.attention > 40
ORDER BY sa.attention DESC
LIMIT 0,50**
GROUP BY s.id

我不能在条件和排序中使用 sa.attention。为什么？(我删除了标记的部分，查询成功了!)

我必须在查询中更改什么？如果您能对此给出解释，那就太好了!

Answer 1

您通过将 OUTER JOIN 放入您的 WHERE 标准来否定您的 OUTER JOIN。将它移到您的 JOIN 中，您将得到您的 NULL 记录:

SELECT s.*,
    u.username,
    u.fullname,
    c.title AS ctitle,
    c.description AS cdescription,
    sa.attention,
    sp.popularity,
    COUNT(DISTINCT f.id) AS favorites,
    COUNT(DISTINCT st.id) AS stars,
    COUNT(DISTINCT v.id) AS views
FROM shots s
    INNER JOIN users u ON u.id = s.user_id
    INNER JOIN categories c ON c.id = s.cat_id
    LEFT OUTER JOIN(
        SELECT shot_id, round(AVG(count),2) AS attention
        FROM points 
        WHERE date > DATE_SUB(CURDATE(),INTERVAL 2 DAY)
        GROUP BY shot_id
    ) sa ON sa.shot_id = s.id AND sa.attention > 40
    LEFT OUTER JOIN(
        SELECT shot_id, SUM(count) AS popularity
        FROM points 
        GROUP BY shot_id
    ) sp ON sp.shot_id = s.id
    LEFT OUTER JOIN favorites f ON f.shot_id = s.id
    LEFT OUTER JOIN stars st ON st.shot_id = s.id
    LEFT OUTER JOIN views v ON v.shot_id = s.id
WHERE s.library = 1 
GROUP BY s.id
ORDER BY sa.attention DESC
LIMIT 0,50

第二个注意事项，GROUP BY 不能放在最后。我也将它移到了正确的位置。