FIND_IN_SET 带有逗号分隔值的排序条件 [英] FIND_IN_SET with comma seperated values for sorting condition

问题描述

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

I am using find_in_set for fetching data using below query and it works correctly.

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.但是是任意数量的逗号分离的值.谢谢

Here this query SELECT column1 FROM B WHERE id = 21 gives result like '175587,282329' but I want that '175587,282329' whichever highest value will come here that I should use.In this case,It would be 282329.but Is will any number of comma seperated values. Thanks

推荐答案

假设 TableA 看起来是这样

Assuming TableA looks as that

CREATE TABLE A (
    id INT,
    columnA INT
);

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

following approach would give you the desired result (as I understand it) for up to 100 separated values:

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
;

演示

最内层的 SELECT 会创建一个值为 1 到 100 的临时表:

The most inner SELECT creates a temporary table with the values of 1 to 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

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

This method is a usual approach to generate such lists and is fast.

嵌套的SUBSTRING调用负责获取我们的值，用

The nested SUBSTRING call is responsible to get our values, test it with

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

感受一下它的作用.我们用

to get a feeling for what it does. We limit our search with

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

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

because we have one comma less than values and we need the last value too. So the statement

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 中的值列表.

will return the list of the values from the column1 corresponding to id = 21.

剩下的就是将这个列表的最大值简单连接到表A的相应列.

The rest is a simple join on the maximum value of this list to the corresponding column of TableA.

这篇关于FIND_IN_SET 带有逗号分隔值的排序条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！