sql - CASE 语句别名上的 WHERE 子句

Question

我在这里有一个 CASE 表达式，它正在添加一个新列 IsAccountActive带有 bool 值。然后我如何添加一个 where 子句来只查询事件的行？

SELECT 
ma.CustomerID,
ma.FirstName,
ma.LastName,
(
    CASE WHEN (
        sa.Active < 1
            OR
        ma.Active < 1
            OR
        (sa.CancelDate IS NOT NULL AND sa.CancelDate <= GETDATE())
            OR
        (ma.CancelDate IS NOT NULL AND ma.CancelDate <= GETDATE())
            OR
        (sa.ExpireDate IS NOT NULL AND DATEADD(dd, sa.Extension + 1, sa.ExpireDate) <= GETDATE())
    ) THEN
        0
    ELSE
        1
    END
) as IsAccountActive
FROM MasterAccounts ma
INNER JOIN SubAccounts sa
ON sa.CustomerID = ma.CustomerID
INNER JOIN MasterAccountData mad
ON mad.CustomerID = sa.CustomerID
WHERE mad.AccountDataTypeID = 20001
AND mad.Data = ''
AND IsAccountActive = 1

Answer 1

WHERE 子句对 SELECT 列表中的别名一无所知。

原因: MS SQL 服务器中有一个逻辑处理顺序( see link here )，其中 WHERE 在 SELECT 列表之前计算

所以一种方法是创建一个内部查询并将 where 放在外面。

Select * from
(
SELECT 
ma.CustomerID,
ma.FirstName,
ma.LastName,
(
    CASE WHEN (
        sa.Active < 1
            OR
        ma.Active < 1
            OR
        (sa.CancelDate IS NOT NULL AND sa.CancelDate <= GETDATE())
            OR
        (ma.CancelDate IS NOT NULL AND ma.CancelDate <= GETDATE())
            OR
        (sa.ExpireDate IS NOT NULL AND DATEADD(dd, sa.Extension + 1, sa.ExpireDate) <= GETDATE())
    ) THEN
        0
    ELSE
        1
    END
) as IsAccountActive
FROM MasterAccounts ma
INNER JOIN SubAccounts sa
ON sa.CustomerID = ma.CustomerID
INNER JOIN MasterAccountData mad
ON mad.CustomerID = sa.CustomerID
WHERE mad.AccountDataTypeID = 20001
AND mad.Data = '')T
where T.IsAccountActive = 1

另一种方法是将 case 放在 where 子句中，例如

SELECT 
    ma.CustomerID,
    ma.FirstName,
    ma.LastName
    FROM MasterAccounts ma
    INNER JOIN SubAccounts sa
    ON sa.CustomerID = ma.CustomerID
    INNER JOIN MasterAccountData mad
    ON mad.CustomerID = sa.CustomerID
    WHERE mad.AccountDataTypeID = 20001
    AND mad.Data = ''
    AND CASE WHEN (
            sa.Active < 1
                OR
            ma.Active < 1
                OR
            (sa.CancelDate IS NOT NULL AND sa.CancelDate <= GETDATE())
                OR
            (ma.CancelDate IS NOT NULL AND ma.CancelDate <= GETDATE())
                OR
            (sa.ExpireDate IS NOT NULL AND DATEADD(dd, sa.Extension + 1, sa.ExpireDate) <= GETDATE()))
         THEN
            0
        ELSE
            1 
        END =1