如何筛选仅含use=0标签且指定group_id的产品ID？

需求说明 #

从给定的5张数据表中，筛选出仅关联use值为0的标签、属于指定group_id（此处为55）的产品ID，同时排除所有同时关联了use值为1标签的产品。

数据表结构 #

1. Product表 #

CREATE TABLE Product (id, color) AS
  SELECT 234322, 'green' FROM DUAL UNION ALL
  SELECT 636634, 'blue'  FROM DUAL;

2. Product_label表 #

3. Labels表 #

4. Region表 #

5. Product_region表 #

期望结果 #

现有SQL问题 #

当前编写的SQL无法过滤掉带有use=1标签的产品，代码如下：

LISTAGG(DISTINCT C.LABEL_NAME, ',') WITHIN GROUP (ORDER BY B.PRODUCT_ID)
OVER (PARTITION BY B.PRODUCT_ID) AS NAMES,
LISTAGG(DISTINCT C.USE, ',') WITHIN GROUP (ORDER BY B.PRODUCT_ID)
OVER (PARTITION BY B.PRODUCT_ID) AS SELECTED

FROM PRODUCT A

LEFT JOIN PRODUCT_LABEL B
ON A.ID = B.PRODUCT_ID

LEFT JOIN LABELS C
ON B.LABEL_ID = C.ID

LEFT OUTER JOIN REGION D
ON C.REGION_ID = D.ID

WHERE D.GROUP_ID = '55';

修改后的SQL及说明 #

修正后的SQL代码 #

SELECT 
    pl.product_id,
    LISTAGG(DISTINCT l.label_name, ',') WITHIN GROUP (ORDER BY l.label_name) AS label_id,
    MAX(l.use) AS use,
    MAX(r.group_id) AS group_id
FROM Product_label pl
JOIN Labels l ON pl.label_id = l.label_name
JOIN Region r ON l.region_id = r.id
WHERE r.group_id = '55'
AND pl.product_id NOT IN (
    -- 排除所有关联过use=1标签的产品
    SELECT DISTINCT pl2.product_id
    FROM Product_label pl2
    JOIN Labels l2 ON pl2.label_id = l2.label_name
    WHERE l2.use = 1
)
GROUP BY pl.product_id;

关键修改点说明 #

1. 排除含use=1标签的产品：通过子查询找出所有关联过use=1标签的产品ID，用NOT IN排除这些产品，确保剩余产品仅关联use=0的标签。
2. 聚合逻辑调整：改用GROUP BY替代窗口函数，直接对每个符合条件的产品聚合标签名称，避免窗口函数保留不符合条件的记录。
3. 表关联优化：去掉了与Product表的关联（因为期望结果中的232897不在该表中，但属于有效产品），如果需要强制关联Product表，可将FROM Product_label pl改为FROM Product p LEFT JOIN Product_label pl ON p.id = pl.product_id，并调整GROUP BY字段。

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