如何将SQL查询结果与请求对象的index值关联映射？

问题描述 #

现有如下SQL查询模板：

SELECT id 
FROM offers 
WHERE description LIKE "%X1%" 
OR description LIKE "%X2%" 
OR ... description LIKE "%X999%"

该WHERE子句是通过循环遍历如下对象数组动态构建的：

[
{index: 0, values: ["X1", "X2", "X3"]},
{index: 1, values: ["X4", "X5"]},
...
{index: N, values: ["X997", "X998", "X999"]}
]

数组中不同对象可能包含相同的X值，现在需要从offers表中找出匹配所有X值的行，但无法将查询结果与数组中对应的index值映射。需求是：要么在SELECT结果中返回WHERE子句中匹配的X值，要么返回对应的index值。

解决方案 #

方案1：返回匹配的X值及对应index（推荐） #

将原有的多OR条件查询拆分为多个子查询，通过UNION ALL合并结果，每个子查询对应数组中一个index下的一个X值，同时在SELECT中明确指定对应的index和匹配的X值。

示例生成的SQL（基于数组片段）：

SELECT id, 0 AS `index`, 'X1' AS matched_value
FROM offers
WHERE description LIKE "%X1%"
UNION ALL
SELECT id, 0 AS `index`, 'X2' AS matched_value
FROM offers
WHERE description LIKE "%X2%"
UNION ALL
SELECT id, 1 AS `index`, 'X4' AS matched_value
FROM offers
WHERE description LIKE "%X4%"
UNION ALL
SELECT id, 1 AS `index`, 'X5' AS matched_value
FROM offers
WHERE description LIKE "%X5%"

• 优点：能清晰展示每一行匹配的具体X值和对应的index，即使同一行匹配多个条件，也会返回所有匹配的关联关系
• 注意：如果需要去重（比如同一个id匹配同一组条件只返回一次），可以在最终结果外层添加SELECT DISTINCT id, index, matched_value FROM (...) AS temp

方案2：仅返回对应index（无需具体X值） #

如果不需要知道具体匹配的X值，只需要关联index，可以用两种方式实现：

方式A：用CASE WHEN返回第一个匹配的index

SELECT 
    id,
    CASE
        WHEN description LIKE "%X1%" OR description LIKE "%X2%" OR description LIKE "%X3%" THEN 0
        WHEN description LIKE "%X4%" OR description LIKE "%X5%" THEN 1
        ...
        WHEN description LIKE "%X997%" OR description LIKE "%X998%" OR description LIKE "%X999%" THEN N
    END AS `index`
FROM offers
WHERE 
    description LIKE "%X1%" 
    OR description LIKE "%X2%" 
    OR ... 
    OR description LIKE "%X999%"

• 缺点：如果一行匹配多个index对应的条件，只会返回第一个匹配的index，无法体现所有关联关系

方式B：用UNION ALL返回所有匹配的index

SELECT id, 0 AS `index`
FROM offers
WHERE description LIKE "%X1%" OR description LIKE "%X2%" OR description LIKE "%X3%"
UNION ALL
SELECT id, 1 AS `index`
FROM offers
WHERE description LIKE "%X4%" OR description LIKE "%X5%"
UNION ALL
...
SELECT id, N AS `index`
FROM offers
WHERE description LIKE "%X997%" OR description LIKE "%X998%" OR description LIKE "%X999%"

• 优点：能返回所有匹配的index，即使一行对应多个index
• 注意：同样可以根据需求添加DISTINCT去重

总结 #

如果需要完整的关联映射（包括具体匹配的X值和对应index），优先选择方案1的UNION ALL方式；如果仅需关联index且接受只返回第一个匹配项，可使用方案2的CASE WHEN方式；若需所有匹配的index则用方案2的UNION ALL方式。

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