Oracle SQL中含重复值的IN查询如何返回对应数量的记录

问题原因解释 #

IN子句的本质是判断左侧字段值是否存在于右侧的集合中，数据库在执行查询时会自动对IN列表内的重复值做去重处理，IN ('1','2','2')在执行阶段等价于IN ('1','2')。而你的table1中c1是主键，每个值仅对应唯一一行数据，因此最终仅返回2条匹配结果，不会重复返回c1='2'的行。

实现需求的查询方案 #

你要实现按IN列表内的条目数量返回对应结果，不能直接用IN子句实现，需要先构造包含重复值的输入集合，再和原表做关联查询，示例如下：

方案1：用UNION ALL构造输入集合（兼容所有Oracle版本） #

SELECT 1
FROM (
    -- 构造和你需求一致的带重复值的输入列表
    SELECT '1' AS input_val FROM DUAL
    UNION ALL
    SELECT '2' AS input_val FROM DUAL
    UNION ALL
    SELECT '2' AS input_val FROM DUAL
) input_list
INNER JOIN table1 t ON t.c1 = input_list.input_val;

这种写法会保留输入列表的重复条目，关联后c1='2'的行会匹配两次输入列表的'2'，最终返回3条结果。

方案2：用JSON_TABLE解析列表（适用于Oracle 12c及以上版本） #

如果输入值较多，不想写大量UNION ALL，可以用JSON函数批量解析输入列表：

SELECT 1
FROM table1 t
INNER JOIN JSON_TABLE(
    '["1","2","2"]', -- 把你的输入值按JSON数组格式填写
    '$[*]' COLUMNS input_val VARCHAR2(100) PATH '$'
) input_list ON t.c1 = input_list.input_val;

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