如何在SQLite中不按顺序匹配secondary字段查询mod表的Id

方案一：行转列后分组匹配 #

把每个Id对应的四个Secondary字段转换成多行数据，再和目标值集合匹配，统计每个Id匹配的唯一值数量等于目标值总数，即可定位对应Id：

SELECT Id
FROM (
    -- 将四个Secondary字段拆分为多行
    SELECT Id, Secondary1 AS sec FROM mod
    UNION ALL
    SELECT Id, Secondary2 AS sec FROM mod
    UNION ALL
    SELECT Id, Secondary3 AS sec FROM mod
    UNION ALL
    SELECT Id, Secondary4 AS sec FROM mod
) AS unpivoted_mod
-- 筛选目标值
WHERE sec IN ('speed', 'tenacity', 'defense', 'offense')
GROUP BY Id
-- 确保该Id的四个Secondary值恰好覆盖所有目标值（无遗漏）
HAVING COUNT(DISTINCT sec) = 4;

方案二：多条件直接匹配 #

通过IN判断每个目标值都存在于四个Secondary字段中，同时校验该Id的四个字段没有额外值：

SELECT Id
FROM mod
WHERE 
    -- 每个目标值都在四个Secondary字段中存在
    'speed' IN (Secondary1, Secondary2, Secondary3, Secondary4)
    AND 'tenacity' IN (Secondary1, Secondary2, Secondary3, Secondary4)
    AND 'defense' IN (Secondary1, Secondary2, Secondary3, Secondary4)
    AND 'offense' IN (Secondary1, Secondary2, Secondary3, Secondary4)
    -- 校验四个字段的唯一值数量等于目标值总数（避免存在额外值）
    AND (
        SELECT COUNT(DISTINCT sec)
        FROM (
            SELECT Secondary1 AS sec
            UNION ALL SELECT Secondary2
            UNION ALL SELECT Secondary3
            UNION ALL SELECT Secondary4
        ) AS sec_list
    ) = 4;

补充说明 #

• 两种方案都无需枚举24种排列组合，代码简洁易维护；
• 如果你的场景中Secondary字段可能存在重复值，可根据目标值的重复情况调整计数逻辑（比如用COUNT(*)替代COUNT(DISTINCT)，前提是目标值的重复次数与字段中的重复次数一致）。

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