mysql - FIND_IN_SET 使用逗号分隔值作为排序条件

Question

我正在使用 find_in_set 使用以下查询来获取数据，并且它工作正常。

SELECT * 
FROM A 
WHERE FIND_IN_SET(
column1,
(
 SELECT column1 
 FROM B 
 WHERE  id = 21)
);

这里这个查询SELECT column1 FROM B WHERE id = 21给出了类似'175587,282329'的结果但我想要 '175587,282329' 无论哪个最高值都会出现在这里，我应该使用。在这种情况下，它将是 282329。但是将是任意数量的逗号单独的值。谢谢

Answer 1

假设 TableA 看起来像这样

CREATE TABLE A (
    id INT,
    columnA INT
);

以下方法将为您提供最多 100 个分隔值的所需结果(据我所知):

SELECT * FROM A
INNER JOIN (
    SELECT MAX(t.value) as max_value
    FROM (
        SELECT
            id, 
            SUBSTRING_INDEX(SUBSTRING_INDEX(column1, ',', n.n), ',', -1) value
        FROM B CROSS JOIN (
            -- build for up to 100 separated values
            SELECT 
                a.N + b.N * 10 + 1 AS n
            FROM
                (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
               ,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
            ORDER BY n
        ) n
        WHERE n <= (1 + LENGTH(column1) - LENGTH(REPLACE(column1, ',', '')))
        AND B.id = 21
    ) t
) t1
ON A.columnA = t1.max_value
;

Demo

说明

最里面的SELECT创建一个临时表，其值是1到100:

    SELECT 
        a.N + b.N * 10 + 1 AS n
    FROM
        (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
       ,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
    ORDER BY n

此方法是生成此类列表的常用方法，并且速度很快。

嵌套的 SUBSTRING 调用负责获取我们的值，用它进行测试

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(column1, ',', 1), ',', -1) FROM B
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(column1, ',', 2), ',', -1) FROM B

感受一下它的作用。我们限制我们的搜索

1 + LENGTH(column1) - LENGTH(REPLACE(column1, ',', ''))

因为我们比值少一个逗号，并且我们也需要最后一个值。所以声明

SELECT
    id, 
    SUBSTRING_INDEX(SUBSTRING_INDEX(column1, ',', n.n), ',', -1) value
FROM B CROSS JOIN (
    SELECT 
        a.N + b.N * 10 + 1 AS n
    FROM
        (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
       ,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
    ORDER BY n
) n
WHERE n <= (1 + LENGTH(column1) - LENGTH(REPLACE(column1, ',', '')))
AND B.id = 21

将返回 id = 21 对应的 column1 中的值列表。

剩下的就是将此列表的最大值与 TableA 的相应列进行简单连接。