如何编写SQL筛选符合用户多维度资质的assessment_id

现有assessments表和存储评估受众筛选条件的Assessment_Criteria表，表结构包含assessment_id、criteria_id、criteria_type（可选值为designation、department、city、project）。每个评估可关联多个同类型的筛选条件，例如某评估可关联2个designation ID。需求是编写SQL查询，筛选出符合新用户资质的assessment_id。

错误尝试分析 #

首次编写的SQL语句无法得到预期结果：

SELECT assessment_id
FROM Assessment_Criteria
WHERE (criteria_id, criteria_type) = (1, 'designation')
  AND (criteria_id, criteria_type) = (1, 'department');

该语句执行失败的原因很明确：同一行记录不可能同时满足两个互斥的条件——一行的criteria_type要么是designation要么是department，无法同时为两者。

正确解决方案 #

假设新用户的资质是「designation ID为1 且 department ID为1」，以下是几种可行的实现方式：

方法1：分组统计满足条件的类型数量

SELECT assessment_id
FROM Assessment_Criteria
WHERE (criteria_type = 'designation' AND criteria_id = 1)
   OR (criteria_type = 'department' AND criteria_id = 1)
GROUP BY assessment_id
HAVING COUNT(DISTINCT criteria_type) = 2;

• 逻辑：先筛选出所有符合任一条件的记录，再按assessment_id分组。通过COUNT(DISTINCT criteria_type)确保该评估同时覆盖了两种类型的条件（值为2表示designation和department的条件都满足）。
• 适配性：如果需要增加更多条件（比如city ID为5），只需在WHERE中添加对应的OR条件，同时把HAVING中的数值改成3即可。

方法2：使用EXISTS子查询

SELECT DISTINCT ac1.assessment_id
FROM Assessment_Criteria ac1
WHERE EXISTS (
    SELECT 1
    FROM Assessment_Criteria ac2
    WHERE ac2.assessment_id = ac1.assessment_id
      AND ac2.criteria_type = 'designation'
      AND ac2.criteria_id = 1
)
AND EXISTS (
    SELECT 1
    FROM Assessment_Criteria ac3
    WHERE ac3.assessment_id = ac1.assessment_id
      AND ac3.criteria_type = 'department'
      AND ac3.criteria_id = 1
);

• 逻辑：通过两个独立的EXISTS子查询，分别验证当前评估是否存在designation=1和department=1的筛选记录，只有同时满足两个子查询的评估才会被选中。

方法3：自连接表

SELECT DISTINCT ac1.assessment_id
FROM Assessment_Criteria ac1
JOIN Assessment_Criteria ac2 
  ON ac1.assessment_id = ac2.assessment_id
WHERE ac1.criteria_type = 'designation' AND ac1.criteria_id = 1
  AND ac2.criteria_type = 'department' AND ac2.criteria_id = 1;

• 逻辑：将Assessment_Criteria表自连接，匹配同一assessment_id下分别满足两个条件的记录，最后通过DISTINCT去重得到唯一的评估ID。

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