使用CASE在WHERE子句中 - 数据参数SQL Server

Question

我正在尝试验证这个示例，如果日期为空，则从另一个字段选择日期。以下是查询：

SELECT
    *
FROM
    OrderPublish OP
    INNER JOIN Advertising AD ON
        AD.IdOrderPublish = OP.IdOrderPublish
    INNER JOIN Client Cli ON
        Cli.IdClient = OP.IdClient
    LEFT JOIN AdvertisingInserted AII ON
        AD.IdAdvertisingInserted = AII.IdAdvertisingInserted
    LEFT JOIN TypeProduct TP ON
        AII.IdTypeProduct = TP.IdTypeProduct
    INNER JOIN Publication PUB ON
        PUB.IdAdvertisingInserted = AII.IdAdvertisingInserted
WHERE
    TP.IdTypeProduct in (5,7)
    AND CASE WHEN PUB.DtIni IS NULL
        THEN
            OP.DtInc >= Isnull('08/18/2013', OP.DtInc)
        ELSE
            PUB.DtIni >= Isnull('08/18/2013', PUB.DtIni)
        END
    AND CASE PUB.DtFinal WHEN NULL
        THEN
            OP.DtInc <= Isnull('08/23/2013', OP.DtInc)
        ELSE
            PUB.DtFinal <= Isnull('08/23/2013', PUB.DtFinal)
        END

- D. Scott

---

亲爱的，如果你在同一个 AII 表格上使用 INNER JOIN，而 AII 为空，则 LEFT OUTER JOIN 将失败，你将失去行。你确定吗？ - Joe Taras

Answer 1

在您的情况下，您可以使用COALESCE()来实现这一点，但如果您想在WHERE条件中使用CASE，也可以这样做。将匹配条件移至CASE语句外部，您需要CASE语句返回要比较的值，而不是比较本身。这需要对每个CASE语句进行加倍：

SELECT
    *
FROM
    OrderPublish OP
    INNER JOIN Advertising AD ON
        AD.IdOrderPublish = OP.IdOrderPublish
    INNER JOIN Client Cli ON
        Cli.IdClient = OP.IdClient
    LEFT JOIN AdvertisingInserted AII ON
        AD.IdAdvertisingInserted = AII.IdAdvertisingInserted
    LEFT JOIN TypeProduct TP ON
        AII.IdTypeProduct = TP.IdTypeProduct
    INNER JOIN Publication PUB ON
        PUB.IdAdvertisingInserted = AII.IdAdvertisingInserted
WHERE
    TP.IdTypeProduct in (5,7)
    AND CASE WHEN PUB.DtIni IS NULL
        THEN
            OP.DtInc
        ELSE
            PUB.DtIni 
        END
    >=  CASE WHEN PUB.DtIni IS NULL
        THEN
            Isnull('08/18/2013', OP.DtInc)
        ELSE
            Isnull('08/18/2013', PUB.DtIni)
        END

    AND CASE PUB.DtFinal WHEN NULL
        THEN
            OP.DtInc 
        ELSE
            PUB.DtFinal 
        END
    <= CASE PUB.DtFinal WHEN NULL
        THEN
            Isnull('08/23/2013', OP.DtInc)
        ELSE
            Isnull('08/23/2013', PUB.DtFinal)
        END    

这是如何在WHERE条件中使用CASE语句，但您可以使用AND（）和OR（）条件以及COALESCE（）而无需使用CASE。此外，您的ISNULL（）语句是反过来的，'08/18/2013'永远不会为NULL，因此字段不会被评估，您可能意味着：Isnull（OP.DtInc，'08/23/2013'）。

- Hart CO

Answer 2

你可以尝试使用Coalesce函数替代Case语句：http://msdn.microsoft.com/en-us/library/ms190349.aspx

SELECT
    *
FROM
    OrderPublish OP
    INNER JOIN Advertising AD ON
        AD.IdOrderPublish = OP.IdOrderPublish
    INNER JOIN Client Cli ON
        Cli.IdClient = OP.IdClient
    LEFT JOIN AdvertisingInserted AII ON
        AD.IdAdvertisingInserted = AII.IdAdvertisingInserted
    LEFT JOIN TypeProduct TP ON
        AII.IdTypeProduct = TP.IdTypeProduct
    INNER JOIN Publication PUB ON
        PUB.IdAdvertisingInserted = AII.IdAdvertisingInserted
WHERE
    TP.IdTypeProduct in (5,7)
    AND Coalesce(PUB.DtIni,OP.DtInc)>= Isnull('08/18/2013', OP.DtInc)
    AND Coalesce(PUB.DtFinal,OP.DtInc)<= Isnull('08/23/2013', OP.DtInc)

- Sonam