使用CASE函数实现人员多维度评分及排名的技术方案咨询

Got it, let's walk through exactly how to build this scoring and ranking system using SQL's CASE function—super straightforward once you break it down.

First, for each of your evaluation criteria, you'll use a CASE statement to translate the fixed categorical values into 1-5 scores. Let's use your education example, plus a couple more common criteria to make it concrete:

SELECT
  full_name,
  -- Education scoring: AS=1, BS=2, MS=3, PhD=4, Other=5 (adjust weights as needed!)
  CASE
    WHEN education = 'AS' THEN 1
    WHEN education = 'BS' THEN 2
    WHEN education = 'MS' THEN 3
    WHEN education = 'PhD' THEN 4
    ELSE 5 -- Catch-all for unlisted values
  END AS education_score,
  -- Years of experience scoring: <2=1, 2-5=2, 6-10=3, 11-15=4, >15=5
  CASE
    WHEN years_experience < 2 THEN 1
    WHEN years_experience BETWEEN 2 AND 5 THEN 2
    WHEN years_experience BETWEEN 6 AND 10 THEN 3
    WHEN years_experience BETWEEN 11 AND 15 THEN 4
    ELSE 5
  END AS experience_score,
  -- Certification status scoring: None=1, Partial=2, Full=3, Advanced=4, Expert=5
  CASE
    WHEN certification = 'None' THEN 1
    WHEN certification = 'Partial' THEN 2
    WHEN certification = 'Full' THEN 3
    WHEN certification = 'Advanced' THEN 4
    WHEN certification = 'Expert' THEN 5
    ELSE 1 -- Default to lowest if unknown
  END AS certification_score
FROM employee_evaluations;

Pro tip: Adjust the score values and category mappings to match your actual priority—maybe you want PhD to be 5 instead of 4, just tweak the CASE clauses!

Next, wrap that initial query in a CTE (Common Table Expression) or subquery to sum up all the individual dimension scores into a total:

WITH scored_individuals AS (
  SELECT
    full_name,
    CASE WHEN education = 'AS' THEN 1 WHEN education = 'BS' THEN 2 WHEN education = 'MS' THEN 3 WHEN education = 'PhD' THEN 4 ELSE 5 END AS education_score,
    CASE WHEN years_experience <2 THEN1 WHEN years_experience BETWEEN2 AND5 THEN2 WHEN years_experience BETWEEN6 AND10 THEN3 WHEN years_experience BETWEEN11 AND15 THEN4 ELSE5 END AS experience_score,
    CASE WHEN certification = 'None' THEN1 WHEN certification = 'Partial' THEN2 WHEN certification = 'Full' THEN3 WHEN certification = 'Advanced' THEN4 WHEN certification = 'Expert' THEN5 ELSE1 END AS certification_score
  FROM employee_evaluations
)
SELECT
  full_name,
  education_score,
  experience_score,
  certification_score,
  education_score + experience_score + certification_score AS total_score
FROM scored_individuals;

Finally, add a ranking function to sort everyone by their total score. You have two main options here:

• RANK(): Leaves gaps if multiple people have the same score (e.g., two people with total 10 get rank 2, next person gets rank 4)
• DENSE_RANK(): No gaps (e.g., two people with total 10 get rank 2, next person gets rank 3)

Here's how to add it:

WITH scored_individuals AS (
  SELECT
    full_name,
    CASE WHEN education = 'AS' THEN 1 WHEN education = 'BS' THEN 2 WHEN education = 'MS' THEN 3 WHEN education = 'PhD' THEN 4 ELSE 5 END AS education_score,
    CASE WHEN years_experience <2 THEN1 WHEN years_experience BETWEEN2 AND5 THEN2 WHEN years_experience BETWEEN6 AND10 THEN3 WHEN years_experience BETWEEN11 AND15 THEN4 ELSE5 END AS experience_score,
    CASE WHEN certification = 'None' THEN1 WHEN certification = 'Partial' THEN2 WHEN certification = 'Full' THEN3 WHEN certification = 'Advanced' THEN4 WHEN certification = 'Expert' THEN5 ELSE1 END AS certification_score
  FROM employee_evaluations
),
total_scores AS (
  SELECT
    full_name,
    education_score + experience_score + certification_score AS total_score
  FROM scored_individuals
)
SELECT
  full_name,
  total_score,
  RANK() OVER (ORDER BY total_score DESC) AS overall_rank -- Use DESC if higher score = better rank
FROM total_scores
ORDER BY overall_rank;

Just swap out employee_evaluations with your actual table name, and adjust the CASE clauses to match all your specific criteria and their score mappings. That's it—you'll have a ranked list of all your individuals based on the combined scores!

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