SQL查询WHERE子句添加位置咨询及语句拆解请求

原查询结构拆解 #

整个查询分为两层：

• 内层子查询（命名为mytable）：关联add_design和like_unlike表，统计每个设计的点赞数，按设计ID分组后按点赞数降序排序。
• 外层主查询：将统计好点赞数的mytable与用户表user、设计文档表add_design_doc关联，输出包含用户信息、设计信息的结果，最后按用户ID分组取前4条。

条件添加位置 #

你需要的status=1 AND home='Yes'是对add_design表的过滤条件，应该放在内层子查询的WHERE子句中——也就是在add_design和like_unlike关联完成后、GROUP BY之前的位置。这样能提前过滤掉不符合要求的设计，避免后续统计和关联无效数据。

修改后的完整SQL #

SELECT mytable.*, CONCAT(fname, ' ',  lname) AS user_name, user.image AS user_image, 
    user.fname, user.lname, add_design_doc.image as product_image 
FROM
(
    SELECT 
        add_design.id, add_design.user_id,  title, category, COUNT(add_design.user_id) AS total_likes 
    FROM 
        add_design INNER JOIN 
        like_unlike ON (add_design.id = like_unlike.product_id AND
                        like_unlike.type = '1')
    -- 新增的过滤条件
    WHERE add_design.status = 1 AND add_design.home = 'Yes'
    GROUP BY 
        add_design.id 
    ORDER BY
        total_likes DESC ) AS mytable INNER JOIN 
    
    user ON mytable.user_id = user.id LEFT OUTER JOIN 
    add_design_doc ON mytable.id = add_design_id
GROUP BY
    user_id LIMIT 4;

内容的提问来源于stack exchange，提问作者consolenine