SQL查询的WHERE子句中的CASE语句。有点复杂的COUNT子句
问题描述:
这是在sql server 2005.我有两个表:customers
和orders
。存在具有参数minimumorders
(0或大于零是可能值)的存储过程。我想避免像上面那样的IF ELSE
块,并且使用CASE
声明。有什么方法可以在WHERE
子句中使用CASE
语句来避免代码中的IF ELSE
块?此时,重复两个IF
& ELSE
块中的5行。我不想让他们重复。我期待着像下面这样的查询。SQL查询的WHERE子句中的CASE语句。有点复杂的COUNT子句
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 --------------------
答
考虑使用联接和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
答
考虑使用一个窗口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
所有这些子查询都会使这个运行速度非常慢并且数据量很大。考虑使用一些连接重构 – 2011-12-20 20:45:55