如何在SQL Server中统计两个字符串的公共字符数量

嘿，这个需求挺实用的！统计两个字符串的公共字符数量，得先明确两种常见场景：

• 场景1：统计去重后的公共字符个数（比如"aab"和"abb"的公共去重字符是a、b，共2个）
• 场景2：统计包含重复的公共字符总数量（比如刚才的例子里，a在两个字符串里分别出现2次和1次，取最小1次；b分别出现1次和2次，取最小1次，总共2次）

下面我给你分几种主流数据库来写实现方案，你按需选用：

MySQL/MariaDB（8.0+支持递归CTE） #

咱们用递归CTE把两个字符串拆成单个字符的集合，再做统计：

场景1：去重后的公共字符数 #

WITH RECURSIVE split_a AS (
    -- 拆分字符串A的每个字符
    SELECT 
        id,
        SUBSTRING(str_a, 1, 1) AS char_val,
        1 AS pos
    FROM your_table
    WHERE str_a IS NOT NULL AND LENGTH(str_a) > 0
    UNION ALL
    SELECT 
        id,
        SUBSTRING(str_a, pos + 1, 1) AS char_val,
        pos + 1 AS pos
    FROM split_a
    WHERE pos < LENGTH(str_a)
),
split_b AS (
    -- 拆分字符串B的每个字符
    SELECT 
        id,
        SUBSTRING(str_b, 1, 1) AS char_val,
        1 AS pos
    FROM your_table
    WHERE str_b IS NOT NULL AND LENGTH(str_b) > 0
    UNION ALL
    SELECT 
        id,
        SUBSTRING(str_b, pos + 1, 1) AS char_val,
        pos + 1 AS pos
    FROM split_b
    WHERE pos < LENGTH(str_b)
)
-- 关联两个拆分后的集合，统计去重的公共字符
SELECT 
    a.id,
    COUNT(DISTINCT a.char_val) AS unique_common_chars
FROM split_a a
JOIN split_b b ON a.id = b.id AND a.char_val = b.char_val
GROUP BY a.id;

场景2：包含重复的公共字符总数量 #

WITH RECURSIVE split_a AS (
    SELECT 
        id,
        SUBSTRING(str_a, 1, 1) AS char_val,
        1 AS pos
    FROM your_table
    WHERE str_a IS NOT NULL AND LENGTH(str_a) > 0
    UNION ALL
    SELECT 
        id,
        SUBSTRING(str_a, pos + 1, 1) AS char_val,
        pos + 1 AS pos
    FROM split_a
    WHERE pos < LENGTH(str_a)
),
split_b AS (
    SELECT 
        id,
        SUBSTRING(str_b, 1, 1) AS char_val,
        1 AS pos
    FROM your_table
    WHERE str_b IS NOT NULL AND LENGTH(str_b) > 0
    UNION ALL
    SELECT 
        id,
        SUBSTRING(str_b, pos + 1, 1) AS char_val,
        pos + 1 AS pos
    FROM split_b
    WHERE pos < LENGTH(str_b)
)
-- 先统计每个字符在两个字符串中的出现次数，再取最小值求和
SELECT 
    a.id,
    SUM(LEAST(a.char_count, b.char_count)) AS total_common_chars
FROM (
    SELECT id, char_val, COUNT(*) AS char_count
    FROM split_a
    GROUP BY id, char_val
) a
JOIN (
    SELECT id, char_val, COUNT(*) AS char_count
    FROM split_b
    GROUP BY id, char_val
) b ON a.id = b.id AND a.char_val = b.char_val
GROUP BY a.id;

PostgreSQL #

PostgreSQL有现成的字符串拆分函数，实现起来更简洁：

场景1：去重后的公共字符数 #

SELECT 
    a.id,
    COUNT(DISTINCT a.char_val) AS unique_common_chars
FROM (
    -- 拆分字符串A为单个字符
    SELECT id, unnest(string_to_array(str_a, '')) AS char_val
    FROM your_table
    WHERE str_a IS NOT NULL AND str_a != ''
) a
JOIN (
    -- 拆分字符串B为单个字符
    SELECT id, unnest(string_to_array(str_b, '')) AS char_val
    FROM your_table
    WHERE str_b IS NOT NULL AND str_b != ''
) b ON a.id = b.id AND a.char_val = b.char_val
GROUP BY a.id;

场景2：包含重复的公共字符总数量 #

SELECT 
    a.id,
    SUM(LEAST(a.char_count, b.char_count)) AS total_common_chars
FROM (
    -- 统计字符串A中每个字符的出现次数
    SELECT id, char_val, COUNT(*) AS char_count
    FROM your_table, unnest(string_to_array(str_a, '')) AS char_val
    WHERE str_a IS NOT NULL AND str_a != ''
    GROUP BY id, char_val
) a
JOIN (
    -- 统计字符串B中每个字符的出现次数
    SELECT id, char_val, COUNT(*) AS char_count
    FROM your_table, unnest(string_to_array(str_b, '')) AS char_val
    WHERE str_b IS NOT NULL AND str_b != ''
    GROUP BY id, char_val
) b ON a.id = b.id AND a.char_val = b.char_val
GROUP BY a.id;

SQL Server #

SQL Server同样用递归CTE来拆分字符串：

场景1：去重后的公共字符数 #

WITH RECURSIVE split_a AS (
    SELECT 
        id,
        SUBSTRING(str_a, 1, 1) AS char_val,
        1 AS pos
    FROM your_table
    WHERE str_a IS NOT NULL AND LEN(str_a) > 0
    UNION ALL
    SELECT 
        id,
        SUBSTRING(str_a, pos + 1, 1) AS char_val,
        pos + 1 AS pos
    FROM split_a
    WHERE pos < LEN(str_a)
),
split_b AS (
    SELECT 
        id,
        SUBSTRING(str_b, 1, 1) AS char_val,
        1 AS pos
    FROM your_table
    WHERE str_b IS NOT NULL AND LEN(str_b) > 0
    UNION ALL
    SELECT 
        id,
        SUBSTRING(str_b, pos + 1, 1) AS char_val,
        pos + 1 AS pos
    FROM split_b
    WHERE pos < LEN(str_b)
)
SELECT 
    a.id,
    COUNT(DISTINCT a.char_val) AS unique_common_chars
FROM split_a a
INNER JOIN split_b b ON a.id = b.id AND a.char_val = b.char_val
GROUP BY a.id;

场景2：包含重复的公共字符总数量 #

WITH RECURSIVE split_a AS (
    SELECT 
        id,
        SUBSTRING(str_a, 1, 1) AS char_val,
        1 AS pos
    FROM your_table
    WHERE str_a IS NOT NULL AND LEN(str_a) > 0
    UNION ALL
    SELECT 
        id,
        SUBSTRING(str_a, pos + 1, 1) AS char_val,
        pos + 1 AS pos
    FROM split_a
    WHERE pos < LEN(str_a)
),
split_b AS (
    SELECT 
        id,
        SUBSTRING(str_b, 1, 1) AS char_val,
        1 AS pos
    FROM your_table
    WHERE str_b IS NOT NULL AND LEN(str_b) > 0
    UNION ALL
    SELECT 
        id,
        SUBSTRING(str_b, pos + 1, 1) AS char_val,
        pos + 1 AS pos
    FROM split_b
    WHERE pos < LEN(str_b)
)
SELECT 
    a.id,
    SUM(LEAST(a.char_count, b.char_count)) AS total_common_chars
FROM (
    SELECT id, char_val, COUNT(*) AS char_count
    FROM split_a
    GROUP BY id, char_val
) a
INNER JOIN (
    SELECT id, char_val, COUNT(*) AS char_count
    FROM split_b
    GROUP BY id, char_val
) b ON a.id = b.id AND a.char_val = b.char_val
GROUP BY a.id;

注意事项 #

• 把your_table替换成你的实际表名，str_a、str_b替换成对应的字符串字段名
• 所有方案都处理了字符串为NULL或空的情况，避免报错
• 如果你的数据库版本不支持递归CTE（比如MySQL 5.x），可以用数字表来拆分字符串，不过这种方法比较繁琐，建议升级到支持CTE的版本

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