基于CASE语句编写WHERE子句报错：k.Author标识符无法绑定

首先，你的错误有两个核心原因：

1. WHERE子句优先级高于SELECT：你在SELECT里定义的别名（包括k.Author）无法在WHERE中直接引用，因为WHERE在SELECT之前执行，此时这些别名还不存在。
2. 子查询未关联主查询：你写的子查询(SELECT CASE ... FROM CHRT_Note CN)没有和主查询的行关联，它会返回CHRT_Note表的所有行的计算结果，这会导致标量子查询返回多行的错误（即使你没看到这个错误，它逻辑上也是错误的，因为它不会对应到当前行的Note）。

接下来给你两种修正方案：

方案一：使用CTE（公共表表达式）先计算所有字段，再过滤 #

CTE可以先把需要的所有计算字段（包括你的Author和Referal Agent）都计算好，之后再在主查询里过滤，这样就避开了WHERE和SELECT的优先级问题：

WITH CTE_ReferralNotes AS (
    SELECT 
        MCON.MailHeader_DateSent,
        VP.Person_Name AS [Patient Name],
        MCON.MailHeader_Subject,
        COUNT(MCON.MailHeader_ID) AS [Inboxed Mesage],
        MTA.MailDetail_Folder,
        EP.Person_Name AS [To],
        N.Note_DateOccurred,
        CAST(N.Note_SummaryRTF AS NVARCHAR(MAX)) AS [Note Summary],
        -- 计算Referal Agent，合并重复条件减少冗余
        CASE 
            WHEN CAST(N.Note_SummaryRTF AS NVARCHAR(MAX)) LIKE '%ashlee%' THEN 'Ashlee ' + CHAR(10) + 'Castro'
            WHEN CAST(N.Note_SummaryRTF AS NVARCHAR(MAX)) LIKE '%cordova%' OR CAST(N.Note_SummaryRTF AS NVARCHAR(MAX)) LIKE '%Adrian C%' THEN 'Adrian ' + CHAR(10) + 'Cordova'
            WHEN CAST(N.Note_SummaryRTF AS NVARCHAR(MAX)) LIKE '%lyndsay%' OR CAST(N.Note_SummaryRTF AS NVARCHAR(MAX)) LIKE '%Lyndsay%' THEN 'Lyndsay ' + CHAR(10) + 'Frommer'
            WHEN CAST(N.Note_SummaryRTF AS NVARCHAR(MAX)) LIKE '%rivera%' OR CAST(N.Note_SummaryRTF AS NVARCHAR(MAX)) LIKE '%Margaret%' THEN 'Rivera ' + CHAR(10) + 'Margaret'
            WHEN CAST(N.Note_SummaryRTF AS NVARCHAR(MAX)) LIKE '%Jtroy%' THEN 'Jennifer ' + CHAR(10) + 'Troy'
            WHEN CAST(N.Note_SummaryRTF AS NVARCHAR(MAX)) LIKE '%Ann B%' THEN 'Ann ' + CHAR(10) + 'Burdge'
            ELSE 'N/A' 
        END AS [Referal Agent],
        -- 计算Author，关联当前行的Note内容（无需额外子查询）
        CASE 
            WHEN CAST(N.Note_SummaryRTF AS NVARCHAR(MAX)) LIKE '%Coordinator 1, ' + CHAR(10) + 'Referrals%' THEN 'Coordinator 1, Referrals'
            ELSE 'Not A Referral Note' 
        END AS [Author]
    FROM MSG_MailHeader MCON -- 修正原别名笔误：原FROM写的是MH，但后续用的是MCON
    JOIN MSG_MailDetail MTA ON MCON.MailHeader_ID = MTA.MailHeader_ID
    JOIN ENTY_Person EP ON MTA.Entity_ID = EP.Entity_ID
    JOIN TASK_TaskAttachment TA ON MCON.MailHeader_ID = TA.MailHeader_ID
    JOIN View_Patient VP ON TA.Patient_ID = VP.Patient_ID
    JOIN CHRT_Visit CV ON VP.Patient_ID = CV.Patient_ID
    JOIN CHRT_VisitCPT CVC ON CV.Note_ID = CVC.Note_ID
    JOIN CHRT_OtherNote CON ON TA.Patient_ID = CON.Patient_ID
    JOIN CHRT_Note N ON CON.Note_ID = N.Note_ID
    WHERE 
        MTA.MailDetail_Folder = 3
        AND MCON.MailHeader_DateSent BETWEEN '2018-01-22' AND '2018-01-22 23:59:59' -- 使用标准日期格式避免解析错误
        AND EP.Person_Name = 'Coordinator 1, Referrals'
        AND CVC.VisitCPT_Code IN ('...替换为你的CPT代码...')
        AND N.Note_DateOccurred > MCON.MailHeader_DateSent
    GROUP BY 
        VP.Person_Name, 
        MCON.MailHeader_DateSent, 
        MTA.MailDetail_Folder,
        EP.Person_Name, -- 修正原GROUP BY里的笔误：MTA.MailDetail_FolderP.Person_Name
        N.Note_DateOccurred, 
        MCON.MailHeader_Subject, 
        CAST(N.Note_SummaryRTF AS NVARCHAR(MAX))
)
SELECT *
FROM CTE_ReferralNotes
WHERE [Author] = 'Coordinator 1, Referrals';

方案二：将主查询作为子查询，在外层过滤 #

如果你不想用CTE，也可以把整个查询嵌套起来，在外层WHERE里过滤Author：

SELECT *
FROM (
    SELECT 
        MCON.MailHeader_DateSent,
        VP.Person_Name AS [Patient Name],
        MCON.MailHeader_Subject,
        COUNT(MCON.MailHeader_ID) AS [Inboxed Mesage],
        MTA.MailDetail_Folder,
        EP.Person_Name AS [To],
        N.Note_DateOccurred,
        CAST(N.Note_SummaryRTF AS NVARCHAR(MAX)) AS [Note Summary],
        CASE 
            WHEN CAST(N.Note_SummaryRTF AS NVARCHAR(MAX)) LIKE '%ashlee%' THEN 'Ashlee ' + CHAR(10) + 'Castro'
            WHEN CAST(N.Note_SummaryRTF AS NVARCHAR(MAX)) LIKE '%cordova%' OR CAST(N.Note_SummaryRTF AS NVARCHAR(MAX)) LIKE '%Adrian C%' THEN 'Adrian ' + CHAR(10) + 'Cordova'
            WHEN CAST(N.Note_SummaryRTF AS NVARCHAR(MAX)) LIKE '%lyndsay%' OR CAST(N.Note_SummaryRTF AS NVARCHAR(MAX)) LIKE '%Lyndsay%' THEN 'Lyndsay ' + CHAR(10) + 'Frommer'
            WHEN CAST(N.Note_SummaryRTF AS NVARCHAR(MAX)) LIKE '%rivera%' OR CAST(N.Note_SummaryRTF AS NVARCHAR(MAX)) LIKE '%Margaret%' THEN 'Rivera ' + CHAR(10) + 'Margaret'
            WHEN CAST(N.Note_SummaryRTF AS NVARCHAR(MAX)) LIKE '%Jtroy%' THEN 'Jennifer ' + CHAR(10) + 'Troy'
            WHEN CAST(N.Note_SummaryRTF AS NVARCHAR(MAX)) LIKE '%Ann B%' THEN 'Ann ' + CHAR(10) + 'Burdge'
            ELSE 'N/A' 
        END AS [Referal Agent],
        CASE 
            WHEN CAST(N.Note_SummaryRTF AS NVARCHAR(MAX)) LIKE '%Coordinator 1, ' + CHAR(10) + 'Referrals%' THEN 'Coordinator 1, Referrals'
            ELSE 'Not A Referral Note' 
        END AS [Author]
    FROM MSG_MailHeader MCON
    JOIN MSG_MailDetail MTA ON MCON.MailHeader_ID = MTA.MailHeader_ID
    JOIN ENTY_Person EP ON MTA.Entity_ID = EP.Entity_ID
    JOIN TASK_TaskAttachment TA ON MCON.MailHeader_ID = TA.MailHeader_ID
    JOIN View_Patient VP ON TA.Patient_ID = VP.Patient_ID
    JOIN CHRT_Visit CV ON VP.Patient_ID = CV.Patient_ID
    JOIN CHRT_VisitCPT CVC ON CV.Note_ID = CVC.Note_ID
    JOIN CHRT_OtherNote CON ON TA.Patient_ID = CON.Patient_ID
    JOIN CHRT_Note N ON CON.Note_ID = N.Note_ID
    WHERE 
        MTA.MailDetail_Folder = 3
        AND MCON.MailHeader_DateSent BETWEEN '2018-01-22' AND '2018-01-22 23:59:59'
        AND EP.Person_Name = 'Coordinator 1, Referrals'
        AND CVC.VisitCPT_Code IN ('...替换为你的CPT代码...')
        AND N.Note_DateOccurred > MCON.MailHeader_DateSent
    GROUP BY 
        VP.Person_Name, 
        MCON.MailHeader_DateSent, 
        MTA.MailDetail_Folder,
        EP.Person_Name,
        N.Note_DateOccurred, 
        MCON.MailHeader_Subject, 
        CAST(N.Note_SummaryRTF AS NVARCHAR(MAX))
) AS SubQuery
WHERE SubQuery.[Author] = 'Coordinator 1, Referrals';

额外的优化说明 #

• 我修正了原查询里的表别名笔误：比如原FROM是MSG_MailHeader MH，但后续用的是MCON，统一为MCON；GROUP BY里的MTA.MailDetail_FolderP.Person_Name明显是输入错误，改为EP.Person_Name。
• 合并了重复的CASE条件，减少代码冗余，同时不影响逻辑。
• 推荐使用YYYY-MM-DD的标准日期格式，避免不同数据库环境下的日期解析异常。

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