修改视图报错：子查询未用EXISTS时只能指定一个表达式

问题背景 #

需要修改视图v_CheckView，逻辑为：根据PropertySetting表中SettingKey='AutoEnrollmentBasis'对应的SettingValue，执行不同WHERE条件查询d_customer_stay表的SourceStayID、PropertyCode列：

• 当SettingValue='SubscriptionBased'时，基于InsertDate筛选数据
• 其他情况基于DepartureDate筛选数据

原代码执行时报错：Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.，核心原因是CASE语句的THEN/ELSE分支不能直接返回多列子查询的结果，CASE仅支持返回单个值。

可行解决方法 #

方法一：拆分CASE逻辑到每个列

针对每个需要返回的列单独编写CASE语句，确保每个CASE仅返回单个列的值：

ALTER VIEW v_CheckView
AS
SELECT
    -- 处理SourceStayID列
    CASE 
        WHEN (SELECT SettingValue FROM PropertySetting WHERE SettingKey='AutoEnrollmentBasis') = 'SubscriptionBased'
        THEN (SELECT SourceStayID FROM d_customer_stay WHERE SourceStayID=151905 AND InsertDate=GETDATE())
        ELSE (SELECT SourceStayID FROM d_customer_stay WHERE SourceStayID=151906 AND DepartureDate > DATEADD(DAY, DATEDIFF(DAY, 1, GETDATE()), 0))
    END AS SourceStayID,
    -- 处理PropertyCode列
    CASE 
        WHEN (SELECT SettingValue FROM PropertySetting WHERE SettingKey='AutoEnrollmentBasis') = 'SubscriptionBased'
        THEN (SELECT PropertyCode FROM d_customer_stay WHERE SourceStayID=151905 AND InsertDate=GETDATE())
        ELSE (SELECT PropertyCode FROM d_customer_stay WHERE SourceStayID=151906 AND DepartureDate > DATEADD(DAY, DATEDIFF(DAY, 1, GETDATE()), 0))
    END AS PropertyCode

方法二：使用CROSS APPLY关联筛选（推荐）

通过将配置表与目标表关联，避免重复子查询，逻辑更简洁且性能更优：

ALTER VIEW v_CheckView
AS
SELECT
    cs.SourceStayID,
    cs.PropertyCode
FROM
    PropertySetting ps
CROSS APPLY (
    -- 根据配置值动态筛选目标数据
    SELECT SourceStayID, PropertyCode 
    FROM d_customer_stay 
    WHERE 
        (ps.SettingValue = 'SubscriptionBased' 
         AND SourceStayID=151905 
         AND InsertDate=GETDATE())
        OR 
        (ps.SettingValue != 'SubscriptionBased' 
         AND SourceStayID=151906 
         AND DepartureDate > DATEADD(DAY, DATEDIFF(DAY, 1, GETDATE()), 0))
) cs
WHERE
    ps.SettingKey = 'AutoEnrollmentBasis'

这种方式只查询一次PropertySetting表，通过CROSS APPLY根据配置值匹配对应的d_customer_stay数据，避免了重复子查询带来的性能损耗，逻辑也更易维护。

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