如何对比表中两个数组并排序结果？候选人与职位技能匹配咨询

一、假设表结构 #

• 候选人表 candidates：id（主键）、name、skills（数组/JSON数组类型）
• 职位表 jobs：id（主键）、title、skills（数组/JSON数组类型）

二、核心思路 #

通过计算两张表skills字段的交集元素数量，按数量降序排序，即可得到匹配度最高的候选人-职位组合。

三、不同数据库的实现代码 #

PostgreSQL 原生数组实现 #

PostgreSQL支持原生数组类型，可直接用array_intersect计算交集，再用cardinality获取交集元素个数：

SELECT
    c.id AS candidate_id,
    c.name AS candidate_name,
    j.id AS job_id,
    j.title AS job_title,
    cardinality(array_intersect(c.skills, j.skills)) AS match_count
FROM candidates c
CROSS JOIN jobs j
-- 可选：过滤无共同技能的组合
WHERE cardinality(array_intersect(c.skills, j.skills)) > 0
ORDER BY match_count DESC, candidate_id, job_id;

MySQL JSON数组实现 #

若用JSON数组存储技能，可通过JSON_TABLE拆分数组成行，再统计交集数量：

SELECT
    c.id AS candidate_id,
    c.name AS candidate_name,
    j.id AS job_id,
    j.title AS job_title,
    COUNT(s.skill) AS match_count
FROM candidates c
CROSS JOIN jobs j
JOIN JSON_TABLE(c.skills, '$[*]' COLUMNS(skill VARCHAR(50) PATH '$')) s
JOIN JSON_TABLE(j.skills, '$[*]' COLUMNS(skill VARCHAR(50) PATH '$')) js ON s.skill = js.skill
GROUP BY c.id, c.name, j.id, j.title
-- 可选：过滤无共同技能的组合
HAVING COUNT(s.skill) > 0
ORDER BY match_count DESC, candidate_id, job_id;

四、大数据量下的辅助表优化 #

如果数据量较大，交叉连接效率偏低，可提前拆分技能到辅助表并加索引优化：

1. 创建候选人技能辅助表 candidate_skills：candidate_id、skill（添加联合索引 (candidate_id, skill)）
2. 创建职位技能辅助表 job_skills：job_id、skill（添加联合索引 (job_id, skill)）
3. 通过连接统计匹配数：

SELECT
    c.id AS candidate_id,
    c.name AS candidate_name,
    j.id AS job_id,
    j.title AS job_title,
    COUNT(cs.skill) AS match_count
FROM candidates c
JOIN candidate_skills cs ON c.id = cs.candidate_id
JOIN job_skills js ON cs.skill = js.skill
JOIN jobs j ON js.job_id = j.id
GROUP BY c.id, c.name, j.id, j.title
ORDER BY match_count DESC, candidate_id, job_id;

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