如何在SQL存储过程中过滤自定义字段cour与year？

DevOps 小满

2026-7-17

问题描述

我编写了如下SQL代码，通过CASE语句生成了自定义字段cour和year，需要在存储过程中根据这两个字段进行数据筛选。

原查询代码：

SELECT 
    s.kid, ep.exper_id, ep.exper_name, mp.md_name, 
    mo.md_option_id, kk.external_id,
    mv.completed_id,
    COUNT(ce.times_observed) AS countoftimes,
    CASE
        WHEN kid = '7180' THEN '044986'
        WHEN kid = '7800' THEN '044984' 
    END AS cour,
    CASE 
        WHEN CE.yid = '2024' THEN '2023-24'
        WHEN CE.yid = '2023' THEN '2022-23'  
    END AS [year]
FROM
    ks.reqe_md_pool mp, ks.reqe_md_options mo, 
    ks.reqe_md_values mv, ks.reqe_experiences_pool ep,
    ks.reqe_completed_experiences ce
LEFT JOIN
    ks.oasis_person kk ON kk.person_id = ce.student_id
LEFT JOIN  
    ks.oasis_schedule s ON ce.schedule_id = s.schedule_id, 
    ks.reqe_list_version lv 
WHERE 
    ep.exper_id = '1492'
    AND mv.md_id = mp.md_id 
    AND ce.exper_id = ep.exper_id 
    AND ce.completed_id = mv.completed_id 
    AND mv.md_option_id = mo.md_option_id 
    AND ce.list_version_id = lv.list_version_id 
GROUP BY 
    s.kid, s.did, s.cid, s.lid, ep.exper_id, ep.exper_name,
    mp.md_id, mp.md_name, mo.md_option_id, mo.md_option_order, 
    mo.md_option_name, ce.student_id, mv.text_answer, mv.md_value_id,
    mv.completed_id, CE.YID, kk.external_id

我尝试将原查询作为子查询后添加WHERE条件，但写法存在问题：

SELECT * 
FROM
    (*code at the top* ) 
WHERE
    cour = @cour
    AND year = @year

也尝试过使用CROSS APPLY，但cour字段因关联的表使用了LEFT JOIN而未生效：

SELECT 
    s.kid, ep.exper_id, ep.exper_name, mp.md_name, 
    mo.md_option_id, kk.external_id,
    mv.completed_id,
    C.UCFCOURSEID AS cour
FROM
    ks.reqe_md_pool mp, ks.reqe_md_options mo, 
    ks.reqe_md_values mv, ks.reqe_experiences_pool ep,
    ks.reqe_completed_experiences ce
LEFT JOIN
    ks.oasis_person kk ON kk.person_id = ce.student_id
LEFT JOIN  
    ks.oasis_schedule s ON ce.schedule_id = s.schedule_id, 
    ks.reqe_list_version lv 
CROSS APPLY (SELECT 
    CASE WHEN kid='7180' THEN '044986'
         WHEN kid='7800' THEN '044984'
    END AS UCFCOURSEID) C
WHERE 
    ep.exper_id = '1492'
    AND mv.md_id = mp.md_id 
    AND ce.exper_id = ep.exper_id 
    AND ce.completed_id = mv.completed_id 
    AND mv.md_option_id = mo.md_option_id 
    AND ce.list_version_id = lv.list_version_id 
GROUP BY 
    s.kid, s.did, s.cid, s.lid, ep.exper_id, ep.exper_name,
    mp.md_id, mp.md_name, mo.md_option_id, mo.md_option_order, 
    mo.md_option_name, ce.student_id, mv.text_answer, mv.md_value_id,
    mv.completed_id, CE.YID, kk.external_id, C.UCFCOURSEID

请问如何在SQL存储过程中实现对cour和year字段的筛选？

解决方案

方法1：使用CTE封装原查询后筛选

CTE比子查询更易读，可直接引用自定义字段。注意year是SQL关键字，需用方括号包裹：

CREATE PROCEDURE YourProcedureName
    @cour VARCHAR(20),
    @year VARCHAR(10)
AS
BEGIN
    WITH CTE_Results AS (
        SELECT 
            s.kid, ep.exper_id, ep.exper_name, mp.md_name, 
            mo.md_option_id, kk.external_id,
            mv.completed_id,
            COUNT(ce.times_observed) AS countoftimes,
            CASE
                WHEN s.kid = '7180' THEN '044986'
                WHEN s.kid = '7800' THEN '044984' 
            END AS cour,
            CASE 
                WHEN CE.yid = '2024' THEN '2023-24'
                WHEN CE.yid = '2023' THEN '2022-23'  
            END AS [year]
        FROM
            ks.reqe_md_pool mp
            JOIN ks.reqe_md_options mo ON mv.md_option_id = mo.md_option_id
            JOIN ks.reqe_md_values mv ON mv.md_id = mp.md_id
            JOIN ks.reqe_experiences_pool ep ON ce.exper_id = ep.exper_id
            JOIN ks.reqe_completed_experiences ce ON ce.completed_id = mv.completed_id
            JOIN ks.reqe_list_version lv ON ce.list_version_id = lv.list_version_id
            LEFT JOIN ks.oasis_person kk ON kk.person_id = ce.student_id
            LEFT JOIN ks.oasis_schedule s ON ce.schedule_id = s.schedule_id
        WHERE 
            ep.exper_id = '1492'
        GROUP BY 
            s.kid, s.did, s.cid, s.lid, ep.exper_id, ep.exper_name,
            mp.md_id, mp.md_name, mo.md_option_id, mo.md_option_order, 
            mo.md_option_name, ce.student_id, mv.text_answer, mv.md_value_id,
            mv.completed_id, CE.YID, kk.external_id         
    )
    SELECT *
    FROM CTE_Results
    WHERE cour = @cour
      AND [year] = @year;
END

方法2：直接将CASE条件移至WHERE子句（性能更优）

无需封装子查询，直接用原始字段逻辑替代自定义字段，减少查询层级：

CREATE PROCEDURE YourProcedureName
    @cour VARCHAR(20),
    @year VARCHAR(10)
AS
BEGIN
    SELECT 
        s.kid, ep.exper_id, ep.exper_name, mp.md_name, 
        mo.md_option_id, kk.external_id,
        mv.completed_id,
        COUNT(ce.times_observed) AS countoftimes,
        CASE
            WHEN s.kid = '7180' THEN '044986'
            WHEN s.kid = '7800' THEN '044984' 
        END AS cour,
        CASE 
            WHEN CE.yid = '2024' THEN '2023-24'
            WHEN CE.yid = '2023' THEN '2022-23'  
        END AS [year]
    FROM
        ks.reqe_md_pool mp
        JOIN ks.reqe_md_options mo ON mv.md_option_id = mo.md_option_id
        JOIN ks.reqe_md_values mv ON mv.md_id = mp.md_id
        JOIN ks.reqe_experiences_pool ep ON ce.exper_id = ep.exper_id
        JOIN ks.reqe_completed_experiences ce ON ce.completed_id = mv.completed_id
        JOIN ks.reqe_list_version lv ON ce.list_version_id = lv.list_version_id
        LEFT JOIN ks.oasis_person kk ON kk.person_id = ce.student_id
        LEFT JOIN ks.oasis_schedule s ON ce.schedule_id = s.schedule_id
    WHERE 
        ep.exper_id = '1492'
        -- 转换cour参数为原始kid条件
        AND (
            (@cour = '044986' AND s.kid = '7180')
            OR (@cour = '044984' AND s.kid = '7800')
        )
        -- 转换year参数为原始yid条件
        AND (
            (@year = '2023-24' AND CE.yid = '2024')
            OR (@year = '2022-23' AND CE.yid = '2023')
        )
    GROUP BY 
        s.kid, s.did, s.cid, s.lid, ep.exper_id, ep.exper_name,
        mp.md_id, mp.md_name, mo.md_option_id, mo.md_option_order, 
        mo.md_option_name, ce.student_id, mv.text_answer, mv.md_value_id,
        mv.completed_id, CE.YID, kk.external_id         
END

方法3：修正CROSS APPLY写法

用OUTER APPLY替代CROSS APPLY处理LEFT JOIN导致的NULL情况，同时将计算字段加入GROUP BY和WHERE：

CREATE PROCEDURE YourProcedureName
    @cour VARCHAR(20),
    @year VARCHAR(10)
AS
BEGIN
    SELECT 
        s.kid, ep.exper_id, ep.exper_name, mp.md_name, 
        mo.md_option_id, kk.external_id,
        mv.completed_id,
        COUNT(ce.times_observed) AS countoftimes,
        C.UCFCOURSEID AS cour,
        CASE 
            WHEN CE.yid = '2024' THEN '2023-24'
            WHEN CE.yid = '2023' THEN '2022-23'  
        END AS [year]
    FROM
        ks.reqe_md_pool mp
        JOIN ks.reqe_md_options mo ON mv.md_option_id = mo.md_option_id
        JOIN ks.reqe_md_values mv ON mv.md_id = mp.md_id
        JOIN ks.reqe_experiences_pool ep ON ce.exper_id = ep.exper_id
        JOIN ks.reqe_completed_experiences ce ON ce.completed_id = mv.completed_id
        JOIN ks.reqe_list_version lv ON ce.list_version_id = lv.list_version_id
        LEFT JOIN ks.oasis_person kk ON kk.person_id = ce.student_id
        LEFT JOIN ks.oasis_schedule s ON ce.schedule_id = s.schedule_id
        -- OUTER APPLY处理s.kid为NULL的场景
        OUTER APPLY (SELECT 
            CASE WHEN s.kid='7180' THEN '044986'
                 WHEN s.kid='7800' THEN '044984'
            END AS UCFCOURSEID) C
    WHERE 
        ep.exper_id = '1492'
        AND C.UCFCOURSEID = @cour
        AND (
            (@year = '2023-24' AND CE.yid = '2024')
            OR (@year = '2022-23' AND CE.yid = '2023')
        )
    GROUP BY 
        s.kid, s.did, s.cid, s.lid, ep.exper_id, ep.exper_name,
        mp.md_id, mp.md_name, mo.md_option_id, mo.md_option_order, 
        mo.md_option_name, ce.student_id, mv.text_answer, mv.md_value_id,
        mv.completed_id, CE.YID, kk.external_id, C.UCFCOURSEID        
END

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

如何在SQL存储过程中过滤自定义字段cour与year？

方法1：使用CTE封装原查询后筛选 #

方法2：直接将CASE条件移至WHERE子句（性能更优） #

方法3：修正CROSS APPLY写法 #

方法1：使用CTE封装原查询后筛选

方法2：直接将CASE条件移至WHERE子句（性能更优）

方法3：修正CROSS APPLY写法