如何用SQL实现技能集合与角色的精准关联匹配查询

Got it, let's break down how to solve this problem. The main goal is to match a user's skill set to the correct roles, with two critical rules: we only assign roles where the user has all the skills required by the role's skill set, and if the user has every skill in the system, we only give them the CTO role (no subordinate roles).

Here's the core logic we'll follow:

1. Validate Skill Sets: First, find all skill_set entries where the user possesses every skill required by that set.
2. Prioritize CTO Eligibility: If there's a skill set that includes all existing skills (the CTO's requirement), we exclusively use that set and ignore all others.
3. Fetch & Deduplicate Roles: For the valid skill sets (either just the CTO's, or all qualifying ones), pull the associated roles and return a clean, deduplicated list.

We'll use MySQL for this example (the logic translates to other SQL dialects with minor adjustments).

Step 1: Store the User's Skills #

First, create a temporary table to hold the user's skills (this makes it easy to swap out different skill sets for testing):

-- Create temp table for the user's skills (replace with IDs matching their skills)
CREATE TEMPORARY TABLE user_skills (skill_id INT);
INSERT INTO user_skills VALUES 
  (1), (2), (5); -- Example: python, javascript, management

Step 2: Query to Match Roles #

This query uses Common Table Expressions (CTEs) to break down the logic into manageable chunks:

WITH skill_set_stats AS (
    -- Calculate total skills per skill set and how many the user matches
    SELECT 
        ss.id AS skill_set_id,
        COUNT(ss.skill_id) AS total_skills_in_set,
        SUM(CASE WHEN us.skill_id IS NOT NULL THEN 1 ELSE 0 END) AS matched_skills
    FROM skill_set ss
    LEFT JOIN user_skills us ON ss.skill_id = us.skill_id
    GROUP BY ss.id
),
valid_skill_sets AS (
    -- Filter to only skill sets where the user has ALL required skills
    SELECT skill_set_id
    FROM skill_set_stats
    WHERE matched_skills = total_skills_in_set
),
total_available_skills AS (
    -- Get count of all skills in the system (to identify the CTO's skill set)
    SELECT COUNT(*) AS total_skills FROM skill
),
cto_qualifying_set AS (
    -- Check if the user qualifies for CTO (skill set with every skill)
    SELECT vs.skill_set_id
    FROM valid_skill_sets vs
    JOIN skill_set_stats sss ON vs.skill_set_id = sss.skill_set_id
    JOIN total_available_skills tas ON sss.total_skills_in_set = tas.total_skills
)
-- Final query: prioritize CTO if eligible, else return all valid roles
SELECT DISTINCT r.name AS assigned_role
FROM (
    SELECT skill_set_id FROM cto_qualifying_set
    UNION ALL
    SELECT skill_set_id FROM valid_skill_sets
    WHERE NOT EXISTS (SELECT 1 FROM cto_qualifying_set)
) AS selected_skill_sets
JOIN default_role dr ON selected_skill_sets.skill_set_id = dr.skill_set_id
JOIN role r ON dr.role_id = r.id
ORDER BY assigned_role;

Testing Your Examples #

Let's confirm this works with your sample inputs:

• Input: ['python'] → Temp table has (1) → Returns Junior Software Engineer
• Input: ['python', 'javascript'] → Temp table has (1,2) → Returns Software Engineer
• Input: ['ec2'] → Temp table has (3) → Returns DevOps Engineer
• Input: ['python', 'javascript', 'management'] → Temp table has (1,2,5) → Returns Software Engineer, Tech Lead
• Input: All skills → Temp table has (1,2,3,4,5) → Returns CTO

Your current schema works, but a couple of tweaks will make it more maintainable:

1. Add a name column to skill_set (e.g., "Junior SE Python Skills", "CTO Core Skills") to make debugging and manual updates easier.
2. Add foreign key constraints between tables (e.g., skill_set.skill_id references skill.id) to enforce data integrity and prevent invalid entries.

Example of updating the skill_set table:

ALTER TABLE skill_set ADD COLUMN name VARCHAR(64) NOT NULL AFTER id;
-- Update existing sets with meaningful names
UPDATE skill_set SET name = 'Junior SE (Python)' WHERE id = 1;
UPDATE skill_set SET name = 'Software Engineer' WHERE id = 3;
UPDATE skill_set SET name = 'CTO Full Skill Set' WHERE id = 8;

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