SQL Server 2008 版OVER(... Rows Unbounded Preceding) [英] SQL Server 2008 version of OVER(... Rows Unbounded Preceding)

问题描述

寻求帮助将其转换为 SQL Server 2008 友好，因为我无法解决.我试过交叉应用和内部连接(不是说我做对了)无济于事......有什么建议吗?

Looking for help in converting this to SQL Server 2008 friendly as I just can't work it out. I've tried cross applies and inner joins (not saying I did them right) to no avail... Any suggestions?

这本质上是有一个库存表和一个订单表.并将两者结合起来，告诉我在股票被拿走后该选择什么(有关更多详细信息，请参阅我之前的问题 更多详情)

What this essentially does is have a table of stock and a table of orders. and combine the two to show me what to pick once the stock is taken away (see my previous question for more details More Details)

WITH ADVPICK
     AS (SELECT 'A'                  AS PlaceA,
                placeb,
                CASE
                  WHEN picktime = '00:00' THEN '07:00'
                  ELSE ISNULL(picktime, '12:00')
                END                  AS picktime,
                Cast(product AS INT) AS product,
                prd_description,
                -qty                 AS Qty
         FROM   t_pick_orders
         UNION ALL
         SELECT 'A'               AS PlaceA,
                placeb,
                '0',
                Cast(code AS INT) AS product,
                NULL,
                stock
         FROM   t_pick_stock),
     STOCK_POST_ORDER
     AS (SELECT *,
                Sum(qty)
                  OVER (
                    PARTITION BY placeb, product
                    ORDER BY picktime ROWS UNBOUNDED PRECEDING ) AS new_qty
         FROM   ADVPICK)
SELECT *,
       CASE
         WHEN new_qty > qty THEN new_qty
         ELSE qty
       END AS order_shortfall
FROM   STOCK_POST_ORDER
WHERE  new_qty < 0
ORDER  BY placeb,
          picktime,
          product  

现在按顺序分区的总和是 SQL Server 2012+ 但是我有两台服务器在 2008 上运行，因此需要将其转换...

Now the whole sum over partition by order by is SQL Server 2012+ however I have two servers that run on 2008 and so need it converted...

预期结果:

+--------+--------+----------+---------+-----------+-------+---------+-----------------+
| PlaceA | PlaceB | Picktime | product | Prd_Descr |  qty  | new_qty | order_shortfall |
+--------+--------+----------+---------+-----------+-------+---------+-----------------+
| BW     | AMES   | 16:00    |    1356 | Product A | -1330 |     -17 |             -17 |
| BW     | AMES   | 16:00    |      17 | Product B |   -48 |     -42 |             -42 |
| BW     | AMES   | 17:00    |    1356 | Product A |  -840 |    -857 |            -840 |
| BW     | AMES   | 18:00    |    1356 | Product A |  -770 |   -1627 |            -770 |
| BW     | AMES   | 18:00    |      17 | Product B |  -528 |    -570 |            -528 |
| BW     | AMES   | 19:00    |    1356 | Product A |  -700 |   -2327 |            -700 |
| BW     | AMES   | 20:00    |    1356 | Product A |  -910 |   -3237 |            -910 |
| BW     | AMES   | 20:00    |    8009 | Product C |  -192 |     -52 |             -52 |
| BW     | AMES   | 20:00    |     897 | Product D |   -90 |     -10 |             -10 |
+--------+--------+----------+---------+-----------+-------+---------+-----------------+

推荐答案

一种直接的方法是在 CROSS APPLY 中使用相关子查询.

One straight-forward way to do it is to use a correlated sub-query in CROSS APPLY.

如果您的桌子或多或少大，那么您的下一个问题将是如何使它变快.PlaceB, Product, PickTime INCLUDE (Qty) 上的索引应该会有所帮助.但是，如果你的表真的很大，光标会更好.

If your table is more or less large, then your next question would be how to make it fast. Index on PlaceB, Product, PickTime INCLUDE (Qty) should help. But, if your table is really large, cursor would be better.

WITH
ADVPICK
AS
(
    SELECT 'A' as PlaceA,PlaceB, case when PickTime = '00:00' then '07:00' else isnull(picktime,'12:00') end as picktime, cast(Product as int) as product, Prd_Description, -Qty AS Qty FROM t_pick_orders
    UNION ALL
    SELECT 'A' as PlaceA,PlaceB, '0', cast(Code as int) as product, NULL, Stock FROM t_pick_stock
)
,stock_post_order
AS
(
    SELECT
        *
    FROM
        ADVPICK AS Main
        CROSS APPLY
        (
            SELECT SUM(Sub.Qty) AS new_qty
            FROM ADVPICK AS Sub
            WHERE
                Sub.PlaceB = Main.PlaceB
                AND Sub.Product = Main.Product
                AND T.PickTime <= Main.PickTime
        ) AS A
)
SELECT
    *,
    CASE WHEN new_qty > qty THEN new_qty ELSE qty END AS order_shortfall
FROM
    stock_post_order
WHERE
    new_qty < 0
ORDER BY PlaceB, picktime, product;

哦，如果 (PlaceB, Product, PickTime) 不是唯一的，你会得到与使用 SUM() OVER 的原始查询有些不同的结果.如果您需要完全相同的结果，则需要使用一些额外的列(如 ID)来解决关系.

Oh, and if (PlaceB, Product, PickTime) is not unique, you'll get somewhat different results to original query with SUM() OVER. If you need exactly same results, you need to use some extra column (like ID) to resolve the ties.

这篇关于SQL Server 2008 版OVER(... Rows Unbounded Preceding)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！