Oracle SQL Developer多对多/任意匹配查询问题求助

核心思路 #

要实现需求，关键是先将t_one中逗号分隔的list_val字段拆分为单行数据，再与t_two进行关联，同时满足两张表的日期范围条件。

解决方案SQL语句 #

方法1：使用XMLTABLE拆分字符串（Oracle 11gR2+支持） #

SELECT 
    t_one.item,
    t_two.item AS t_two_item,
    t_one.value || ' ' || t_two.value AS combined_value
FROM t_one
-- 拆分list_val：先去掉前后双引号，再按", "分割为多行
CROSS JOIN XMLTABLE(
    REGEXP_REPLACE(t_one.list_val, '"', '')
    COLUMNS column_value VARCHAR2(10) PATH '.'
) xt
-- 关联t_two，匹配拆分后的item值
JOIN t_two 
    ON TRIM(xt.column_value) = t_two.item
WHERE 
    -- given_date在t_one的date_1和date_2之间
    TO_DATE('08/08/22', 'MM/DD/YY') BETWEEN TO_DATE(t_one.date_1, 'MM/DD/YY') AND TO_DATE(t_one.date_2, 'MM/DD/YY')
    -- given_date同时在t_two的date_1和date_2之间
    AND TO_DATE('08/08/22', 'MM/DD/YY') BETWEEN TO_DATE(t_two.date_1, 'MM/DD/YY') AND TO_DATE(t_two.date_2, 'MM/DD/YY')
ORDER BY t_one.item, t_two.item;

方法2：使用CONNECT BY拆分字符串 #

SELECT 
    t_one.item,
    t_two.item AS t_two_item,
    t_one.value || ' ' || t_two.value AS combined_value
FROM (
    SELECT 
        t_one.item,
        t_one.value,
        -- 拆分list_val：先去引号，再按逗号分割取第level个值
        TRIM(REGEXP_SUBSTR(REGEXP_REPLACE(t_one.list_val, '"', ''), '[^,]+', 1, level)) AS list_item
    FROM t_one
    CONNECT BY 
        -- 生成与逗号数量+1相等的行数
        level <= REGEXP_COUNT(REGEXP_REPLACE(t_one.list_val, '"', ''), ',') + 1
        -- 避免跨行循环，保持原表每行的独立性
        AND PRIOR t_one.item = t_one.item
        AND PRIOR SYS_GUID() IS NOT NULL
    WHERE 
        TO_DATE('08/08/22', 'MM/DD/YY') BETWEEN TO_DATE(t_one.date_1, 'MM/DD/YY') AND TO_DATE(t_one.date_2, 'MM/DD/YY')
) split_t_one
JOIN t_two 
    ON split_t_one.list_item = t_two.item
WHERE 
    TO_DATE('08/08/22', 'MM/DD/YY') BETWEEN TO_DATE(t_two.date_1, 'MM/DD/YY') AND TO_DATE(t_two.date_2, 'MM/DD/YY')
ORDER BY split_t_one.item, t_two.item;

结果说明 #

当given_date为08/08/22时，两条SQL都会返回以下结果：

关键细节说明 #

1. 字符串拆分：list_val包含双引号和空格，需先用REGEXP_REPLACE去掉双引号，再用TRIM去除每个拆分值前后的空格，确保与t_two.item准确匹配。
2. 日期处理：原表日期为字符串格式，必须用TO_DATE转换为日期类型再进行范围比较，避免字符串排序导致的逻辑错误。
3. 性能提示：如果t_one数据量较大，建议优先使用XMLTABLE，其性能通常优于CONNECT BY方式；若需频繁执行该查询，可考虑将拆分逻辑封装为函数或创建视图。

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