在TSQL中按卖出订单减去

Question

我有两个表，如下所示：

表1：

ID_M, Date, Reservation
001,   2014,   5 
001,   2015,    10
001,   2016,    18
002,   2015,    6
002,   2016,    22

表2：

ID_M, ID_type, Priority,Product_Total
001,   1111,       2,        10
001,   2222,       3,        15
001,   3333,       1,         8
002,   1111,       2,        12
002,   2222,       1,        16

可以计算预留的重新分区ID_TYPE吗？表2的优先级表示预留的顺序，优先级1是先预留，依此类推。是否可以在T-SQL中创建一个表/视图/CTE，如下所示？

ID_M, ID_Type, DATE, Reservation
001, 3333,     2014, 5,
001, 3333,     2015, 3,
001, 1111,     2015, 7,
001, 1111,     2016, 3,
001, 2222,     2016, 15,
002, 2222,     2015, 6,
002, 2222,     2016, 10,
002, 1111,     2016, 12,

这里，预留是根据表2中的产品总数和表1中预留的产品数量来计算的。例如，2014年，我们在表1中为ID_M = 001预留了5，我们在表2中搜索表2中的ID_M = 001，我们首先为优先级为1的产品预留，因此它是ID_type 3333。因此，我们得到所需表格的第一行: 001,3333,2014，5。

在2015年，我们在表1中有10个预订，因此我们首先将2014年的剩余产品用于ID_Type 3333，然后我们使用产品ID_Type 1111，ID_type 3333的左侧产品为3 (8-5)，因此左侧预订7 (10-3)用于ID_Type 1111。因此，我们从所需的表3中获得了第二行和第三行：

001,3333,2015，3,001,1111,2015，7，

Answer 1

要解析我创建的代码，请执行以下操作：

1. 为两个表中的每一行提供唯一的id ( rownumber )
2. 使用行号两次连接两个表，一次是从t1获取值，另一次是从t2
3. 获取值最后，我使用SUM函数从t1中进行加法运算，从t2中减去

CREATE PROC #sp_test --删除环境中的#

作为

--此行在您的环境中不存在-- Start

声明@t1为表(id_m CHAR(3)，date INT，reservation INT)

将@t2声明为表(id_m CHAR(3)，id_type INT，priority INT，product_total INT)

插入@t1 (id_m，日期，预订) SELECT '001‘,2014 ,5

INSERT @t1 (id_m，日期，预订) SELECT '001‘,2015 ,10

INSERT @t1 (id_m，日期，预订) SELECT '001‘,2016 ,18

INSERT @t1 (id_m，日期，预订) SELECT '002‘,2015 ,6

INSERT @t1 (id_m，日期，预订) SELECT '002‘,2016 ,22

INSERT @t2 (id_m，id_type，priority，product_total) SELECT '001‘,1111 ,2 ,10

插入@t2 (id_m，id_type，priority，product_total) SELECT '001‘,2222 ,3 ,15

INSERT @t2 (id_m，id_type，priority，product_total) SELECT '001‘,3333 ,1 ,8

INSERT @t2 (id_m，id_type，priority，product_total) SELECT '002‘,1111 ,2 ,12

INSERT @t2 (id_m，id_type，priority，product_total) SELECT '002‘,2222 ,1 ,16

--此行在您的环境中不存在-- Start

；使用cte_t1

AS (SELECT Row_number() OVER (ORDER BY id_m，date) )行号

，*

来自@t1)，

cte_t2

AS (SELECT Row_number()

OVER (

按id_m排序，优先级)行号，*

来自@t2)，cte_trn1

AS (SELECT DISTINCT 0 level、a.rownumber、b.id_m、b.id_type、b.priority、a.date、a.reservation

从cte_t1 a内部连接cte_t2 b ON a.rownumber = b.rownumber)，cte_trn2

AS (选择DISTINCT 1 level、a.rownumber、b.id_m、b.id_type、b.priority、a.date、b.product_total

从a.rownumber =b.rownumber上的内部连接cte_t2 b)，

cte_union AS (

SELECT * FROM cte_trn1 a UNION ALL SELECT * FROM cte_trn2 b)，cte_final

as (SELECT id_m，id_type，priority，date，Sum(CASE WHEN level =0则保留否则保留* - 1 END) OVER( partition BY id_m ORDER BY rows PRECEDING )*( CASE WHEN level =0 THEN 1 ELSE -1 END ) AS保留自cte_union)

选择id_m、id_type、priority、date、reservation

从按id_m排序的cte_final，优先级

去

EXEC #sp_test

去

DROP PROC #sp_test

Answer 2

此版本使用tally tables“分解”每个表中的记录，以允许范围重叠检测：

我已经检查了其他情况，例如，预订:将INSERT @t1 (ID_M, Date, Reservation) select '001', 2015, 10中的10替换为15 -似乎返回了正确的结果(根据我对问题的理解)。

CREATE TABLE #t1 ( id_m CHAR(3) ,date INT ,reservation INT )
CREATE TABLE #t2 ( id_m CHAR(3) ,id_type INT ,priority INT ,product_total INT )
INSERT #t1 (ID_M, Date, Reservation) select '001', 2014, 5 
INSERT #t1 (ID_M, Date, Reservation) select '001', 2015, 10
INSERT #t1 (ID_M, Date, Reservation) select '001', 2016, 18
INSERT #t1 (ID_M, Date, Reservation) select '002', 2015, 6
INSERT #t1 (ID_M, Date, Reservation) select '002', 2016, 22
INSERT #t2 (ID_M, ID_type, Priority,Product_Total) select '001', 3333, 1, 8
INSERT #t2 (ID_M, ID_type, Priority,Product_Total) select '001', 1111, 2, 10
INSERT #t2 (ID_M, ID_type, Priority,Product_Total) select '001', 2222, 3, 15
INSERT #t2 (ID_M, ID_type, Priority,Product_Total) select '002', 2222, 1, 16
INSERT #t2 (ID_M, ID_type, Priority,Product_Total) select '002', 1111, 2, 12


;WITH
    -- Tally Table start
    lv0 AS (SELECT 0 AS g UNION ALL SELECT 0),
    lv1 AS (SELECT 0 AS g FROM lv0 AS a CROSS JOIN lv0 AS b), -- 4
    lv2 AS (SELECT 0 AS g FROM lv1 AS a CROSS JOIN lv1 AS b), -- 16
    lv3 AS (SELECT 0 AS g FROM lv2 AS a CROSS JOIN lv2 AS b), -- 256
    lv4 AS (SELECT 0 AS g FROM lv3 AS a CROSS JOIN lv3 AS b), -- 65,536
    --lv5 AS (SELECT 0 AS g FROM lv4 AS a CROSS JOIN lv4 AS b), -- 4,294,967,296
    Nums AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n FROM lv4),
    -- Tally Table end

    -- Explode reservations to have the same number of rows as reservation value
    reservations AS(
        SELECT *, ROW_NUMBER() OVER( PARTITION BY id_m ORDER BY date ) AS CalcOrder
        FROM #t1
            INNER JOIN Nums ON n <= reservation ),

    -- Explode products to have the same number of rows as Product_Total value
    products AS(
        SELECT *, ROW_NUMBER() OVER( PARTITION BY id_m ORDER BY Priority ) AS CalcOrder
        FROM #t2
            INNER JOIN Nums ON n <= Product_Total )

SELECT b.id_m, date, reservation AS RequiredReservation, id_type, priority, product_total, COUNT(*) AS Reservation
FROM reservations AS b
    INNER JOIN products AS d ON b.id_m = d.id_m AND b.CalcOrder = d.CalcOrder
GROUP BY b.id_m, id_type, date, reservation, priority, product_total
ORDER BY b.id_m, date, priority, RequiredReservation

另外，@MaurícioPontaltiNeri回答了一些功劳，特别是因为使用了“有序匹配”的想法。

(SELECT Row_number() OVER ( ORDER BY id_m, priority) rownumber
...
FROM cte_t1 a 
   INNER JOIN cte_t2 b 
           ON a.rownumber = b.rownumber