多CTE查询无结果：如何按标签汇总员工培训学分？

我有一张存储员工培训学分的TrainingCredits表，字段为：EmpID, CourseNum, Credits, CompletedDate，示例数据：151, 400, 0.5, 10/1/2022。另有一张Courses课程表，字段为：CourseNum, CourseName, Creditspossible, Tags（CourseNum唯一），示例数据：400, Javascript 101, 3.0, JSC。其中Tags为3位代码（JSC、PRL、SQL），支持多标签逗号分隔。我需要按标签汇总每位员工的总学分。

单个标签查询可行，SQL语句如下：

SELECT
    EmpID,
    sum(Credits)
FROM TrainingCredits
INNER JOIN courses on TrainingCredits.courseNum=courses.courseNum
WHERE
        courses.tag LIKE '%JSC%' 
        GROUP BY EmpID

但重复修改WHERE条件执行效率低，我尝试为每个标签创建CTE：

WITH JSCCreds (CEmpID, JSCcred) AS ( 
    SELECT                            --* this SELECT repeats for each below ...  *--
        EmpID,
        sum(Credits)
    FROM TrainingCredits
        INNER JOIN courses on TrainingCredits.courseNum=courses.CourseNum
    WHERE
                Courses.Tag LIKE '%JSC%' 
        GROUP BY TrainingCredits.EmpID  ),
PRLCreds (PEmpID, PRLCred) As (
...
                Courses.Tag LIKE '%PRL%' ),
SQLCreds (SEmpID, SQLcred) AS (
...
                Courses.Tag LIKE '%SQL%' )
...
SELECT EmpID,
    JSCCreds.JSCcred,
    PRLCreds.PRLcred,
    SQLCreds.SQLcred
FROM TrainingCredits
WHERE
        TrainingCredits.EmpID=JSCCreds.JEmpID
    AND TrainingCredits.EmpID=PRLCreds.PEmpID
    AND TrainingCredits.EmpID=SQLCreds.SEmpID

该查询无结果，我怀疑CTE部分存在错误。请问是否有更优实现方式？我曾考虑将CSV标签改为布尔字段，但希望先获取建议。

方法一：条件聚合（推荐） #

不需要多次查询或CTE，一次关联后用条件聚合直接计算每个标签的总学分，效率最高：

SELECT
    tc.EmpID,
    SUM(CASE WHEN c.Tags LIKE '%JSC%' THEN tc.Credits ELSE 0 END) AS JSC_Total_Credits,
    SUM(CASE WHEN c.Tags LIKE '%PRL%' THEN tc.Credits ELSE 0 END) AS PRL_Total_Credits,
    SUM(CASE WHEN c.Tags LIKE '%SQL%' THEN tc.Credits ELSE 0 END) AS SQL_Total_Credits
FROM TrainingCredits tc
INNER JOIN Courses c ON tc.CourseNum = c.CourseNum
GROUP BY tc.EmpID;

如果员工没有对应标签的学分，会显示0，更符合统计需求。

方法二：修复CTE查询 #

原CTE查询无结果的原因是最后关联时强制要求员工匹配所有标签的CTE（等价于INNER JOIN），但很多员工可能只修了部分标签的课程，应该用全外连接确保所有员工被统计：

WITH JSCCreds AS ( 
    SELECT
        EmpID,
        SUM(Credits) AS JSCcred
    FROM TrainingCredits
    INNER JOIN Courses ON TrainingCredits.CourseNum = Courses.CourseNum
    WHERE Courses.Tags LIKE '%JSC%'
    GROUP BY EmpID
),
PRLCreds AS (
    SELECT
        EmpID,
        SUM(Credits) AS PRLcred
    FROM TrainingCredits
    INNER JOIN Courses ON TrainingCredits.CourseNum = Courses.CourseNum
    WHERE Courses.Tags LIKE '%PRL%'
    GROUP BY EmpID
),
SQLCreds AS (
    SELECT
        EmpID,
        SUM(Credits) AS SQLcred
    FROM TrainingCredits
    INNER JOIN Courses ON TrainingCredits.CourseNum = Courses.CourseNum
    WHERE Courses.Tags LIKE '%SQL%'
    GROUP BY EmpID
)
SELECT
    COALESCE(j.EmpID, p.EmpID, s.EmpID) AS EmpID,
    COALESCE(j.JSCcred, 0) AS JSCcred,
    COALESCE(p.PRLcred, 0) AS PRLcred,
    COALESCE(s.SQLcred, 0) AS SQLcred
FROM JSCCreds j
FULL OUTER JOIN PRLCreds p ON j.EmpID = p.EmpID
FULL OUTER JOIN SQLCreds s ON COALESCE(j.EmpID, p.EmpID) = s.EmpID;

用COALESCE把NULL替换为0，避免统计结果出现空值。

标签结构优化建议 #

CSV格式的Tags字段会导致LIKE '%XXX%'无法利用索引，长期来看影响查询效率。可以考虑两种优化方向：

• 新增布尔字段：IsJSC, IsPRL, IsSQL，查询时直接用等值判断，能利用索引提升速度。
• 建立标准化标签关联表（如CourseTags：CourseNum, Tag），每个标签单独存一行，查询更灵活，也能创建联合索引优化性能。

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