SQLite按指定PRODUCT_NR列表查询，匹配唯一行返回ID否则空值

需求说明 #

给定包含ID、PRODUCT_NR的业务表，需根据指定的PRODUCT_NR列表（支持重复值）执行查询，满足以下规则：

• 若列表中的PRODUCT_NR在业务表中仅匹配1行，返回该PRODUCT_NR及对应ID
• 若列表中的PRODUCT_NR在业务表中匹配多行或无匹配，返回该PRODUCT_NR，ID留空
• 查询结果的顺序必须与输入的PRODUCT_NR列表完全一致

示例数据 #

业务表（假设表名为products）：

ID   PRODUCT_NR    OTHER COLUMNS...
1    999           ...
2    1234          ...
3    001           ...
4    1234          ...

输入的PRODUCT_NR列表：

1234
999
1234
0000

预期查询结果：

PRODUCT_NR    ID
1234            
999           1
1234        
0000

实现SQL语句 #

WITH target_list AS (
  -- 构造带序号的目标产品列表，序号保证结果顺序与输入一致
  SELECT 1 AS seq, '1234' AS PRODUCT_NR UNION ALL
  SELECT 2, '999' UNION ALL
  SELECT 3, '1234' UNION ALL
  SELECT 4, '0000'
),
product_stats AS (
  -- 预统计每个产品在业务表中的匹配行数，同时提取唯一匹配时的ID
  SELECT 
    PRODUCT_NR,
    COUNT(*) AS match_count,
    MAX(ID) AS unique_id
  FROM products
  GROUP BY PRODUCT_NR
)
SELECT 
  tl.PRODUCT_NR,
  -- SQLite不支持IF，用CASE判断是否仅匹配一行
  CASE WHEN ps.match_count = 1 THEN ps.unique_id ELSE NULL END AS ID
FROM target_list tl
LEFT JOIN product_stats ps ON tl.PRODUCT_NR = ps.PRODUCT_NR
ORDER BY tl.seq;

代码说明 #

1. target_list临时表：将输入的PRODUCT_NR列表转换成带seq（序号）的数据集，通过ORDER BY tl.seq确保结果顺序与输入完全一致
2. product_stats临时表：对业务表中的PRODUCT_NR进行分组统计，match_count记录每个产品的匹配行数；若仅匹配1行，MAX(ID)（或MIN(ID)）可直接获取对应的唯一ID
3. 关联与判断：用LEFT JOIN保留目标列表中的所有产品，通过CASE语句根据统计结果返回对应ID或空值

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