如何嵌套WHILE循环

Question

我在处理这些WHILE循环时遇到了问题。我想让我的查询查看每个Product_ID，并计算它在到达后的前12天内每天被购买的次数。库存项目差异很大，从20,000到140,000不同的项目库存取决于日期，季节等。我想显示day01计数的PiD销售，day02等。我认为嵌套循环可能是答案，但我对建议……持开放态度建议？

注意:现在，查询似乎已经中断，并且会永远运行。请原谅我的措辞。

              DECLARE
    @Item_Num01 int,
    @Item_Num02 int,
    @Nomenclature varchar(255),
    @PiD varchar(255),
    @MadeOn datetime,
    @TimeSpan int,   
    @date datetime,
    @count int

           SELECT * FROM TABLE_02 ORDER BY ItemName ASC


    SET @Item_Num01 = 1
    SELECT @Item_Num02 = MAX(INVEN_ID) FROM TABLE_02
    CREATE TABLE TABLE_03(ItemName nvarchar(255), Prod_ID varchar(255), Shipped_Date datetime, 
    Time_Span int, Day int, OrdersCount int, INVEN_ID int)

    WHILE @Item_Num01 <= @Item_Num02
BEGIN
           SET @Nomenclature = 0
    SET @PiD = 0
    SET @MadeOn = 0
    SET @TimeSpan = 0
    SET @count = 0

    SELECT @Nomenclature = ItemName FROM TABLE_02 WHERE INVEN_ID = @Item_Num01
    SELECT @PiD = Product_ID FROM TABLE_02 WHERE INVEN_ID = @Item_Num01
    SELECT @MadeOn = Shipped_Date FROM TABLE_02 WHERE INVEN_ID = @Item_Num01
    SELECT @TimeSpan = Time_Span FROM TABLE_02 WHERE INVEN_ID = @Item_Num01
        SET @date = @MadeOn
       WHILE @date < DATEADD(DAY,12,@MadeOn)
    BEGIN
    SELECT @count = COUNT(INV_STK.Product_ID)
    FROM INV_STK
        WHERE INV_STK.PRODUCT_ID = @PiD
        AND CONVERT(DATE,SoldDate) = CONVERT(DATE,@date)

    INSERT INTO TABLE_03
    VALUES (@Nomenclature, @PiD, @MadeOn, @TimeSpan,DATEDIFF(dd,@MadeOn,@date+@TimeSpan, @count, @Item_Num01)

    SET @date = DATEADD(DAY,1,@date)
 END
    SET @Item_Num01 = @Item_Num01 + 1
  END

SELECT * FROM TABLE_03 ORDER BY INVEN_ID, Day ASC

谢谢

Answer 1

在没有实际样本数据的情况下，我得出了以下结论：

SELECT t.ItemName, t.Product_ID AS Prod_ID, t.Shipped_Date, t.Time_Span, d.NumDays AS [Day], count(i.Product_ID) AS OrdersCount, t.INVEN_ID
FROM table_02 t
    CROSS JOIN (
        SELECT NumDays
        FROM (
            SELECT ROW_NUMBER()OVER(ORDER BY name) NumDays
            FROM master..spt_values
            ) a
        WHERE NumDays <= 12
    ) d
    LEFT JOIN INV_STK i ON i.Product_ID = t.Product_ID
        and CONVERT(DATE,i.SoldDate) = CONVERT(DATE,DATEADD(DAY,d.NumDays,t.Shipped_Date))
GROUP BY t.ItemName, t.Product_ID, t.Shipped_Date, t.Time_Span, d.NumDays, t.INVEN_ID

本质上，您希望确保拥有全部12天，因此使用内部联接生成人工行，并使用dateadd函数中的day值将左联接用于inventory表。在那之后，就是所有的聚合和分组。这应该服务于您的目的。不过，我需要样本输入和所需的输出来进一步改进。