在mysql中比较两个逗号分隔的值并获取匹配的计数

Question

比较mysql中逗号分隔的值，并在countcolumn中获取匹配的输出

例子：

id   values 
 1    1,2,3
 2    3
 3    1,3

通过比较ID 1和2，输出应为1

通过比较ID 2和3，输出应为1

通过比较ID 3和1，输出应为2

Answer 1

首先，您列出的数据实际上应该以一种格式存储，数据库的每一行都存储一个id和一个value，即3元素列表将对应3行。如果对您的结构进行了更改，则以下答案将是有意义的。

就目前而言，这是我整合的一个不错的MySQL小功能，可用于您的目的。这将接受2个参数，这些参数是逗号分隔的列表，并将返回出现在列表2中的列表1中的元素数。

这样做并不能防止在列表2中对列表1中的重复ID进行两次计数（即“ 1,1,2”和“
1,2”将返回值3），但是您可以弄清楚如何合理地调整此值如果您愿意，可以轻松地做到这一点。

要使用此功能，只需执行

SELECT 
    countMatchingElements( '3' , '1,3' ) AS testCase1
    countMatchingElements( '1,2,3' , '1,3' ) AS testCase2
    countMatchingElements( '3' , '1,2,3' ) AS testCase3;

存储的功能逻辑如下

CREATE DEFINER = `yourUserGoesHere`@`%` FUNCTION `countMatchingElements`(inFirstList VARCHAR(1000), inSecondList VARCHAR(1000))
 RETURNS tinyint(3) unsigned
    NO SQL
    DETERMINISTIC
    SQL SECURITY INVOKER
BEGIN
    DECLARE numReturn TINYINT UNSIGNED DEFAULT 0;
    DECLARE idsInFirstList TINYINT UNSIGNED;
    DECLARE currentListItem VARCHAR(255) DEFAULT '';
    DECLARE currentID TINYINT UNSIGNED;

    SET idsInFirstList = ( CHAR_LENGTH( inFirstList ) + 1 ) - CHAR_LENGTH( REPLACE( inFirstList , ',' , '' ) );
    SET currentID = 1;

    -- Loop over inFirstList, and for each element that is in inSecondList increment numReturn
    firstListLoop: REPEAT

        SET currentListItem = SUBSTRING_INDEX( SUBSTRING_INDEX( inFirstList , ',' , currentID ) , ',' , -1 );

        IF FIND_IN_SET( currentListItem , inSecondList ) THEN

            SET numReturn = numReturn + 1;

        END IF;

        SET currentID = currentID + 1;

    UNTIL currentID > idsInFirstList
    END REPEAT firstListLoop;

    RETURN numReturn;
END