MySQL Join查询问题：筛选含指定商品的订单时需返回订单关联的全部商品

我完全懂你的困扰——想筛选出包含特定商品的订单，同时要展示该订单的全部商品，但直接加搜索条件后，订单里不符合条件的商品就消失了，GROUP_CONCAT只能拿到符合要求的那部分，对吧？

问题根源 #

你要是直接在原来的查询里加WHERE products.title = 'p1'，本质是在过滤products表的记录，这会把原本的LEFT JOIN变成类似INNER JOIN的效果：只有匹配到p1的商品记录会被保留，订单里其他不满足条件的商品直接被排除，最终GROUP_CONCAT自然只能拼接出p1，而不是整个订单的完整商品列表。

解决方案：先筛选订单，再关联全部商品 #

我们可以拆成两步来实现需求：

1. 先找出所有包含目标商品（比如p1）的订单ID
2. 基于这些订单ID，去关联它们的所有商品信息，再做GROUP_CONCAT

下面是对应的SQL语句：

SELECT 
    o.*, 
    GROUP_CONCAT(p.title SEPARATOR ' ') AS products
FROM orders o
-- 第一步：筛选出包含p1的订单ID
WHERE o.id IN (
    SELECT DISTINCT op.order_id
    FROM order_products op
    JOIN products p_inner ON p_inner.id = op.product_id
    WHERE p_inner.title = 'p1'
)
-- 第二步：关联该订单的所有商品
LEFT JOIN order_products op ON op.order_id = o.id
LEFT JOIN products p ON p.id = op.product_id
GROUP BY o.id;

你也可以用JOIN的方式实现，逻辑完全一致：

SELECT 
    o.*, 
    GROUP_CONCAT(p.title SEPARATOR ' ') AS products
FROM orders o
JOIN (
    -- 子查询得到符合条件的订单ID
    SELECT DISTINCT op.order_id
    FROM order_products op
    JOIN products p_inner ON p_inner.id = op.product_id
    WHERE p_inner.title = 'p1'
) filtered_orders ON filtered_orders.order_id = o.id
LEFT JOIN order_products op ON op.order_id = o.id
LEFT JOIN products p ON p.id = op.product_id
GROUP BY o.id;

效果验证 #

执行这个查询后，你会得到期望的结果：

（注：你的例子里订单2的商品是p4，并不包含p1，所以不会出现在结果里，之前你提到的当前结果里订单2显示p1应该是笔误啦）

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