存储过程：即使订单日期不同，三列中的值也相同

Question

我在这里列出了一个存储过程问题::

例如客户 A 有： i.) 前 30 天内有 5 个订单， ii.) 前 60 天内有 5 个订单， iii.) 前 90 天内有 5 个订单

因此结果应该是：

     A.) First 30 Days: Five orders
     B.) First 60 Days: Five + Five = 10 Orders
     C.) First 90 Days: Five + Five + Five = 15 Orders

但是，我的存储过程在所有 30,60 和 90 天内显示“15 个订单”。您能帮助我吗，以便我的存储过程在 30 天内显示“5 个订单”，“60 天内有 10 个订单”，在 90 天内显示“15 个订单”。 [以下是代码的一部分] 感谢您的帮助。

---

 IF (@CUSTOMERID IS NOT NULL)
 BEGIN

CREATE TABLE #TEMP_D
(
CUSTOMERID VARCHAR(20),
ORDERID INT,
PRODUCTID INT,
QUANTITY VARCHAR(20)
)
INSERT INTO #TEMP_F(CUSTOMERID,ORDERID,PRODUCTID,QUANTITY)
 (CUSTOMERID VARCHAR(20),ORDERID INT,PRODUCTID INT,QUANTITY VARCHAR(20))
 INSERT INTO #TEMP_D(CUSTOMERID,ORDERID,PRODUCTID,QUANTITY)
 SELECT C.CUSTOMERID, COUNT(O.ORDERID),COUNT(OD.PRODUCTID),
        RANKING = CASE
        WHEN SUM(OD.QUANTITY*OD.UNITPRICE)>250 THEN 'REGULAR'
        WHEN SUM(OD.QUANTITY*OD.UNITPRICE)< 250 THEN 'MODERATE'
        END
        FROM CUSTOMERS C 
        INNER JOIN
        ORDERS O ON C.CUSTOMERID=O.CUSTOMERID
        INNER JOIN
        [ORDER DETAILS] OD ON O.ORDERID=OD.ORDERID
        WHERE (C.CUSTOMERID = @CUSTOMERID)
        GROUP BY C.CUSTOMERID



CREATE TABLE #TEMP_E
(
CUSTOMERID VARCHAR(20),
ORDERID INT,
PRODUCTID INT,
QUANTITY VARCHAR(20)
)
INSERT INTO #TEMP_F(CUSTOMERID,ORDERID,PRODUCTID,QUANTITY)
 (CUSTOMERID VARCHAR(20),ORDERID INT,PRODUCTID INT,QUANTITY VARCHAR(20))
 INSERT INTO #TEMP_D(CUSTOMERID,ORDERID,PRODUCTID,QUANTITY)
 SELECT C.CUSTOMERID, COUNT(O.ORDERID),COUNT(OD.PRODUCTID),
        RANKING = CASE
        WHEN SUM(OD.QUANTITY*OD.UNITPRICE)>250 THEN 'REGULAR'
        WHEN SUM(OD.QUANTITY*OD.UNITPRICE)< 250 THEN 'MODERATE'
        END
        FROM CUSTOMERS C 
        INNER JOIN
        ORDERS O ON C.CUSTOMERID=O.CUSTOMERID
        INNER JOIN
        [ORDER DETAILS] OD ON O.ORDERID=OD.ORDERID
        WHERE (C.CUSTOMERID = @CUSTOMERID)
        GROUP BY C.CUSTOMERID


CREATE TABLE #TEMP_F
(
CUSTOMERID VARCHAR(20),
ORDERID INT,
PRODUCTID INT,
QUANTITY VARCHAR(20)
)
INSERT INTO #TEMP_F(CUSTOMERID,ORDERID,PRODUCTID,QUANTITY)
 (CUSTOMERID VARCHAR(20),ORDERID INT,PRODUCTID INT,QUANTITY VARCHAR(20))
 INSERT INTO #TEMP_D(CUSTOMERID,ORDERID,PRODUCTID,QUANTITY)
 SELECT C.CUSTOMERID, COUNT(O.ORDERID),COUNT(OD.PRODUCTID),
        RANKING = CASE
        WHEN SUM(OD.QUANTITY*OD.UNITPRICE)>250 THEN 'REGULAR'
        WHEN SUM(OD.QUANTITY*OD.UNITPRICE)< 250 THEN 'MODERATE'
        END
        FROM CUSTOMERS C 
        INNER JOIN
        ORDERS O ON C.CUSTOMERID=O.CUSTOMERID
        INNER JOIN
        [ORDER DETAILS] OD ON O.ORDERID=OD.ORDERID
        WHERE (C.CUSTOMERID = @CUSTOMERID)
        GROUP BY C.CUSTOMERID


SELECT D.CUSTOMERID, D.ORDERID AS 'ORDERS 30 DAYD',D.PRODUCTID AS 'PRODUCTS 30 DAYS',D.QUANTITY, E.CUSTOMERID, E.ORDERID AS 'ORDERS 60                 
   DAYS',E.PRODUCTID AS 'PRODUCTS 60 DAYS',E.QUANTITY,  F.CUSTOMERID, F.ORDERID AS 'ORDERS 90 DAYD',F.PRODUCTID AS 'PRODUCTS 90 DAYS',F.QUANTITY
   FROM #TEMP_D D
   INNER JOIN  #TEMP_E E  ON D.CUSTOMERID=E.CUSTOMERID
   INNER JOIN #TEMP_F F ON E.CUSTOMERID = F.CUSTOMERID

结尾

Answer 1

我可能在这里遗漏了一些东西，但是您对临时表的 3 个查询似乎都是同一个查询。您没有在 WHERE 子句中按日期进行过滤，因此它们都会得出相同的结果（15 个订单）。