SQL技术问题：无法将COUNT统计语句与多表关联查询整合

可行整合方案 #

方案1：子查询筛选员工ID #

将统计符合条件的员工ID的逻辑作为子查询，嵌入主查询的筛选条件中，确保只返回技能数≥2的员工的技能明细：

SELECT e.Employeename, s.skillDescription
FROM EmployeeSkills_t k
INNER JOIN Employee_t e ON k.EmployeeID = e.EmployeeID
INNER JOIN Skill_t s ON k.skillID = s.skillID
WHERE k.EmployeeID IN (
    SELECT employeeID
    FROM EmployeeSkills_t
    GROUP BY employeeID
    HAVING COUNT(*) > 1
)
ORDER BY e.Employeename, s.skillDescription;

方案2：窗口函数直接计算技能数 #

利用窗口函数在明细行中直接计算对应员工的总技能数，再筛选符合条件的记录，无需额外关联子查询：

SELECT Employeename, skillDescription
FROM (
    SELECT 
        e.Employeename, 
        s.skillDescription,
        COUNT(*) OVER (PARTITION BY k.EmployeeID) AS skill_count
    FROM EmployeeSkills_t k
    INNER JOIN Employee_t e ON k.EmployeeID = e.EmployeeID
    INNER JOIN Skill_t s ON k.skillID = s.skillID
) AS temp_result
WHERE skill_count > 1
ORDER BY Employeename, skillDescription;

方案3：EXISTS关联查询 #

通过EXISTS子查询判断当前员工是否满足技能数≥2的条件，适合大表场景（若EmployeeSkills_t.EmployeeID有索引，性能更优）：

SELECT e.Employeename, s.skillDescription
FROM EmployeeSkills_t k
INNER JOIN Employee_t e ON k.EmployeeID = e.EmployeeID
INNER JOIN Skill_t s ON k.skillID = s.skillID
WHERE EXISTS (
    SELECT 1
    FROM EmployeeSkills_t k2
    WHERE k2.EmployeeID = k.EmployeeID
    GROUP BY k2.EmployeeID
    HAVING COUNT(*) > 1
)
ORDER BY e.Employeename, s.skillDescription;

原问题逻辑冲突说明 #

之前的统计语句是聚合查询（每个员工仅返回一行技能数统计结果），而主查询是明细查询（每个员工的每项技能单独返回一行），直接合并会导致聚合逻辑与明细逻辑冲突，因此需要通过子查询、窗口函数或EXISTS先筛选出符合条件的员工，再关联获取技能明细。

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