如何用单条SQL语句实现含空值的ID区间去重物品组合查询？

现有表结构及数据 #

ID  ITEM
1   banana
1   apple
2   pear
2   grapes
3   carrots
4   parsnips
5   hats
6   scarves

需求说明 #

编写单条SQL语句，返回所有id1（from-id）与id2（to-id）的合法组合（要求id1不为空，且id1 < id2或id2为空），同时获取该ID区间内的去重物品列表：

• 当id2不为空时，包含所有ID在[id1, id2]范围内的物品
• 当id2为空时，包含所有ID >= id1的物品

当前问题 #

已实现ID组合的生成逻辑，但关联物品列表时存在缺陷：

• 区间查询未包含中间ID的物品（比如id1=1、id2=3时，没包含ID=2的物品）
• id2为空时，无法返回所有符合条件的物品

解决方案SQL #

WITH q AS (
    SELECT rownum AS id
    FROM dual 
    CONNECT BY level <= (SELECT MAX(id) FROM items)
    UNION SELECT NULL AS id FROM dual
)
SELECT 
    q1.id AS id1,
    q2.id AS id2,
    LISTAGG(DISTINCT i.item, ', ') WITHIN GROUP (ORDER BY i.item) AS unique_items
FROM q q1
CROSS JOIN q q2
LEFT JOIN items i 
    ON i.id >= q1.id 
    AND (i.id <= q2.id OR q2.id IS NULL)
WHERE q1.id IS NOT NULL
  AND (q1.id < q2.id OR q2.id IS NULL)
GROUP BY q1.id, q2.id
ORDER BY q1.id, q2.id;

逻辑说明 #

1. ID组合生成：通过q CTE生成所有有效ID及null值，再通过交叉连接筛选出符合要求的id1和id2组合
2. 物品关联逻辑：
   • 用LEFT JOIN确保即使区间内没有物品也能保留ID组合
   • 条件i.id >= q1.id AND (i.id <= q2.id OR q2.id IS NULL)处理两种场景：
     • 当id2不为空时，匹配ID在id1到id2之间的物品
     • 当id2为空时，匹配所有ID大于等于id1的物品
3. 去重与拼接：使用LISTAGG(DISTINCT ...)先对物品去重，再按指定顺序拼接成列表

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