使用递归求解一库存问题

• 需求描述：
  

• SQL求解

--构造商品库存测试数据
postgres=# CREATE TABLE tbl1 (
postgres(#     isbn character varying(32),
postgres(#     stock integer,
postgres(#     vendor integer
postgres(# );
CREATE TABLE
postgres=# COMMENT ON TABLE tbl1 IS '商品库存表';
COMMENT
postgres=# COMMENT ON COLUMN tbl1.isbn IS '商品isbn';
COMMENT
postgres=# COMMENT ON COLUMN tbl1.stock IS '商品库存数量';
COMMENT
postgres=# COMMENT ON COLUMN tbl1.vendor IS '供货商ID';
COMMENT
postgres=# INSERT INTO tbl1 (isbn, stock, vendor) VALUES ('9787118078817', 1, 9);
INSERT 0 1
postgres=# INSERT INTO tbl1 (isbn, stock, vendor) VALUES ('9787118078817', 2, 7);
INSERT 0 1
postgres=# INSERT INTO tbl1 (isbn, stock, vendor) VALUES ('9787512108004', 2, 7);
INSERT 0 1
postgres=# INSERT INTO tbl1 (isbn, stock, vendor) VALUES ('9787512108004', 5, 9);
INSERT 0 1
postgres=# INSERT INTO tbl1 (isbn, stock, vendor) VALUES ('9787536473386', 2, 7);
INSERT 0 1
postgres=# INSERT INTO tbl1 (isbn, stock, vendor) VALUES ('9787530660560', 2, 7);
INSERT 0 1
postgres=# INSERT INTO tbl1 (isbn, stock, vendor) VALUES ('9787530660560', 7, 8);
INSERT 0 1
postgres=# INSERT INTO tbl1 (isbn, stock, vendor) VALUES ('9787530660560', 8, 10);
INSERT 0 1
postgres=# SELECT * FROM tbl1;
     isbn      | stock | vendor 
---------------+-------+--------
 9787118078817 |     1 |      9
 9787118078817 |     2 |      7
 9787512108004 |     2 |      7
 9787512108004 |     5 |      9
 9787536473386 |     2 |      7
 9787530660560 |     2 |      7
 9787530660560 |     7 |      8
 9787530660560 |     8 |     10
(8 rows)
--构造商品销售测试数据
postgres=# CREATE TABLE tbl2 (
postgres(#     isbn character varying(32),
postgres(#     stock integer
postgres(# );
CREATE TABLE
postgres=# COMMENT ON TABLE tbl2 IS '商品销售表';
COMMENT
postgres=# COMMENT ON COLUMN tbl2.isbn IS '商品isbn';
COMMENT
postgres=# COMMENT ON COLUMN tbl2.stock IS '商品销售数量';
COMMENT
postgres=# INSERT INTO tbl2 (isbn, stock) VALUES ('9787118078817', 2);
INSERT 0 1
postgres=# INSERT INTO tbl2 (isbn, stock) VALUES ('9787536473386', 2);
INSERT 0 1
postgres=# INSERT INTO tbl2 (isbn, stock) VALUES ('9787530660560', 5);
INSERT 0 1
postgres=# SELECT * FROM tbl2;
     isbn      | stock 
---------------+-------
 9787118078817 |     2
 9787536473386 |     2
 9787530660560 |     5
(3 rows)

postgres=# WITH recursive tmp AS --首先根据商品isbn和库存构造出一个棵查询树，为递归做准备
postgres-# (SELECT t.isbn,
postgres(#        t.stock,
postgres(#        t.vendor,
postgres(#        t.stock2,
postgres(#        t.isbn || '_' || top v_id,  --构建虚拟主键ID
postgres(#        t.isbn || '_' || LAG(top, 1, NULL) OVER(PARTITION BY t.isbn ORDER BY t.stock) v_pid --构建虚拟父节点ID
postgres(#   FROM (SELECT t1.isbn,
postgres(#                t1.stock,
postgres(#                t1.vendor,
postgres(#                t2.stock stock2,
postgres(#                row_number() over(PARTITION BY t1.isbn ORDER BY t1.stock) top
postgres(#           FROM tbl1 t1,
postgres(#                tbl2 t2
postgres(#          WHERE t1.isbn = t2.isbn)t ),
postgres-#  x AS
postgres-#  (SELECT t.isbn,
postgres(#          CASE
postgres(#              WHEN t.stock - t.stock2 <= 0 THEN
postgres(#               0
postgres(#              ELSE
postgres(#               t.stock - t.stock2
postgres(#          END stock,
postgres(#          t.vendor,
postgres(#          (t.stock2 - t.stock) stock2,
postgres(#          t.v_id,
postgres(#          t.v_pid
postgres(#     FROM tmp t
postgres(#    WHERE t.v_pid IS NULL  --从每个商品最小的库存行开始递归
postgres(#   UNION ALL
postgres(#   SELECT tmp.isbn,
postgres(#          CASE
postgres(#              WHEN tmp.stock - x.stock2 <= 0 THEN --如果当前供应商的库存小于销售量，则标记当前供应商的库存为0
postgres(#               0
postgres(#              WHEN x.stock2 < 0 THEN --如果当前销售量为负数，说明前一个节点的库存已大于销售，那么库存无需再减
postgres(#               tmp.stock
postgres(#              ELSE
postgres(#               tmp.stock - x.stock2 --库存-销量=剩余库存
postgres(#          END stock,
postgres(#          tmp.vendor,
postgres(#          (x.stock2 - tmp.stock) stock2, --销量-库存=多出的销量
postgres(#          tmp.v_id,
postgres(#          tmp.v_pid
postgres(#     FROM x,
postgres(#          tmp
postgres(#    WHERE x.v_id = tmp.v_pid)
postgres-# SELECT isbn,stock,vendor FROM x WHERE stock != 0 --过滤掉为0库存的供应商数据
postgres-# UNION ALL --此部分数据为没有销售过的商品数据，直接返回即可
postgres-# SELECT t1.isbn,
postgres-#        t1.stock,
postgres-#        t1.vendor
postgres-#   FROM tbl1 t1
postgres-#   LEFT JOIN tbl2 t2
postgres-#     ON t1.isbn = t2.isbn
postgres-#  WHERE t2.isbn IS NULL;
     isbn      | stock | vendor 
---------------+-------+--------
 9787118078817 |     1 |      7   --销量为2，按照库存从低到高依次减销量，直到销量全部减完
 9787530660560 |     4 |      8
 9787530660560 |     8 |     10
 9787512108004 |     2 |      7
 9787512108004 |     5 |      9
(5 rows)



复制