PostgreSQL：统计第一个数组中出现在第二个数组的元素个数（含重复）

我来帮你搞定这个需求！核心就是要统计第一个数组中**每个元素（包括重复项）**在第二个数组中出现的次数总和，同时还要处理数组为null的情况对吧？下面我以常用的PostgreSQL为例（它对数组的支持非常友好），给你一步步的解决方案：

1. 先明确需求逻辑 #

举个你提到的例子：

• array_1 = {23277,23277}，array_2包含23277 → 结果是2（两个元素都匹配）
• array_1 = {23201,23612,2001,23201}，array_2包含23201和23612 → 结果是3（两个23201+一个23612）
• 如果array_1是null，或者array_2是null → 结果都是0

2. 示例表与数据 #

先创建一个测试表来演示：

CREATE TABLE test_arrays (
    id SERIAL PRIMARY KEY,
    array_1 INT[],
    array_2 INT[]
);

-- 插入测试数据，包含你提到的案例和null情况
INSERT INTO test_arrays (array_1, array_2) VALUES
    ('{23277,23277}', '{23638,23187,23201,23612,28322,27953,23277,23173,23794,28796,23291,23714}'),
    ('{23201,23612,2001,23201}', '{23638,23201,23612}'),
    (NULL, '{1,2,3}'),
    ('{1,2}', NULL);

3. 计算匹配计数的SQL #

用unnest把数组拆成单个元素的行，再统计匹配的数量，同时用COALESCE处理null的情况：

SELECT
    id,
    array_1,
    array_2,
    COALESCE(
        (SELECT COUNT(*)
         FROM unnest(array_1) AS a1(val)
         WHERE a1.val = ANY(array_2)),
        0
    ) AS match_count
FROM test_arrays;

执行后你会得到符合预期的结果：

4. 新增并填充第三列 #

如果要永久添加这一列到表中，可以执行：

-- 新增列，默认值设为0
ALTER TABLE test_arrays ADD COLUMN match_count INT DEFAULT 0;

-- 批量填充数据
UPDATE test_arrays
SET match_count = COALESCE(
    (SELECT COUNT(*)
     FROM unnest(array_1) AS a1(val)
     WHERE a1.val = ANY(array_2)),
    0
);

其他数据库的适配（以MySQL为例） #

如果你用的是MySQL 8.0+，因为它没有原生数组类型，一般用JSON数组存储，那可以用JSON_TABLE来拆分数组，用JSON_CONTAINS检查元素是否存在：

SELECT
    id,
    array_1,
    array_2,
    COALESCE(
        (SELECT COUNT(*)
         FROM JSON_TABLE(array_1, '$[*]' COLUMNS(val INT PATH '$')) AS a1
         WHERE JSON_CONTAINS(array_2, JSON_QUOTE(a1.val))),
        0
    ) AS match_count
FROM test_arrays;

关键点总结 #

• 处理null：用COALESCE把null结果转为0，避免统计结果出现null
• 重复元素统计：拆分数组后每个重复元素都是独立行，COUNT(*)会自动统计所有匹配项
• 元素存在检查：PostgreSQL用ANY(array_2)，MySQL用JSON_CONTAINS，都是高效的数组元素判断方式

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