Oracle SQL查询：筛选同时含指定状态的gather_id并合并状态

需求说明 #

从Sales和Sales_Status两张表中，仅获取对应的info_status同时包含await_collect和collected的gather_id，并将这两个状态以逗号分隔展示。

数据表结构与数据 #

Sales表

gather_id     | id
1459          |8011
1459          |8022
1511          |7011
1511          |7022
1511          |7033
1511          |7044
1911          |1011
1911          |1022
1688          |2011
1688          |2022

Sales_Status表

id      info_status 
8011    await_collect   
8022    collected   
7011    picking 
7022    await_pick  
7033    await_collect   
7044    collected   
1011    await_collect   
1022    collected   
2011    await_pick  
2022    await_collect   

解决方案SQL #

SELECT 
    s.gather_id AS "Gather id",
    LISTAGG(ss.info_status, ',') WITHIN GROUP (ORDER BY ss.info_status) AS info_status
FROM Sales s
JOIN Sales_Status ss ON s.id = ss.id
WHERE ss.info_status IN ('await_collect', 'collected')
GROUP BY s.gather_id
HAVING COUNT(DISTINCT ss.info_status) = 2;

思路解释 #

1. 关联表：通过id字段关联Sales和Sales_Status表，获取每个gather_id对应的状态数据。
2. 过滤状态：用WHERE子句仅保留await_collect和collected这两个目标状态的记录。
3. 分组验证：按gather_id分组，通过HAVING COUNT(DISTINCT ss.info_status) = 2确保该分组同时包含两个目标状态。
4. 拼接状态：使用Oracle的LISTAGG函数，将同一gather_id下的两个状态以逗号分隔拼接展示。

期望输出 #

Gather id       info_status
1459            await_collect,collected
1911            await_collect,collected

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