问题描述
寻求帮助将其转换为 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)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！