SQL 查询的 WHERE 子句中的 CASE 语句。 COUNT 子句有点复杂

Question

这是在 sql server 2005 中。我有两个表：customers 和 orders。有一个存储过程有一个参数minimumorders（0 或大于零是可能的值）。我想避免像上面那样使用 IF ELSE 块，并使用 CASE 语句。有没有办法可以在 WHERE 子句中使用 CASE 语句来避免代码中的 IF ELSE 块？目前IF 和IF 上都有5 行。重复 ELSE 块。我不想让他们重复。我期待着像下面这样的查询。

CREATE PROC STOREDPROC1
@MinimumOrders  INT AS IF @MinimumOrders = 0
BEGIN
    SELECT 
        CUSTOMERID 
        , (SELECT COUNT(ORDERID) FROM ORDERS O WHERE O.CUSTOMERID = C.CUSTOMERID) AS ORDERS
        FROM CUSTOMER C
ENDELSE IF @MinimumOrders = 1
BEGIN
    SELECT 
        CUSTOMERID 
        , (SELECT COUNT(ORDERID) FROM ORDERS O WHERE O.CUSTOMERID = C.CUSTOMERID) AS ORDERS
        FROM CUSTOMER C
            WHERE (SELECT COUNT(ORDERID) FROM ORDERS O WHERE O.CUSTOMERID = C.CUSTOMERID) > 0
END     

我需要一些如下所示的 CASE 语句。不幸的是，它对我不起作用。

-------------------- 12/20/2011 -- 11:52:34 AM -- Tuesday -------------------- 
    SELECT 
        CUSTOMERID 
        , (SELECT COUNT(ORDERID) FROM ORDERS O WHERE O.CUSTOMERID = C.CUSTOMERID) AS ORDERS
        FROM CUSTOMER C
        AND C.customerid=
            CASE @MiniumOrders
                WHEN 0 THEN C.CustomerID
                WHEN 1 THEN 
                    CASE  ( Select COUNT(O.CustomerID) FROM Orders O where O.Customerid = C.customerid    ) 
                        WHEN 0 THEN 0   -- customer id = 0 means no match
                            ELSE C.CustomerID
                    END 
                END 
        -------------------- 12/20/2011 -- 11:52:34 AM -- Tuesday -------------------- 

Answer 1

考虑使用联接和 HAVING 子句：

SELECT
    C.Customer_ID,
    COUNT(O.Customer_ID) AS Orders
FROM
    Customers C
    LEFT JOIN Orders O
        ON C.Customer_ID = Orders.Customer_ID
GROUP BY C.Customer_ID
HAVING COUNT(O.Customer_ID) > @MinimumOrders

Answer 2

考虑使用窗口 COUNT 子句。由于我没有有关您情况的所有详细信息，因此我提供了整个测试用例。您关心的部分是底部的SELECT。

DECLARE @Cust TABLE (
    CustID INT
)

DECLARE @Ord TABLE (
    OrdID INT IDENTITY (1,1),
    CustID INT,
    Qty INT,
    Price MONEY
)

INSERT INTO @Cust 
VALUES
(1),
(2),
(3),
(4)

INSERT INTO @Ord
VALUES
(1, 5, 5.55),
(2,3,3.33),
(2,4,3.22),
(3,4,1.23),
(3,5,5.66),
(3,7,1.22)

DECLARE @MinimumOrders INT = 0

SELECT
    C.CustID,
    CASE
        WHEN COUNT(O.OrdID) OVER(PARTITION BY C.CustID) > @MinimumOrders THEN COUNT(O.OrdID) OVER(PARTITION BY C.CustID)
        ELSE NULL
    END AS Orders,
    O.Qty,
    O.Price
FROM
    @Cust C
    LEFT JOIN @Ord O
        ON C.CustID = O.CustID