使用“有回馈”计算总价

schw4ndi 发表于 Dev

schw4ndi

我有一张表格“ Leistung”（这是英语中的活动），一个表用于两个具有给定数量的活动（benoetigt）之间的匹配。因此，例如，ID为2的活动需要ID为3的三个活动。

每个活动（价格）都有价格。

我现在想计算所有活动（leistung）的总价格，包括适当数量的子活动的价格。

这是我的带有一些测试数据的表：

CREATE TABLE Leistung (
  lnr INTEGER PRIMARY KEY,
  bezeichnung VARCHAR(100) NOT NULL,
  price NUMERIC(7,2) NOT NULL
);

CREATE TABLE benoetigt (
  lnr INTEGER REFERENCES Leistung(lnr),
  benoetigt_lnr INTEGER REFERENCES Leistung(lnr),
  amount INTEGER NOT NULL,
  PRIMARY KEY (lnr,benoetigt_lnr)
);

INSERT INTO Leistung(lnr, bezeichnung, price) VALUES
  (1, 'Discokugel', 100),
  (2, 'Strobolicht', 100),
  (3, 'Verstärker', 90),
  (4, 'Gelaender',50),
  (5, 'Bühne',50),
  (6, 'Bühnenelement',20),
  (7, 'Stromverteiler', 40),
  (8, 'Stromkabel', 20);

INSERT INTO benoetigt (lnr, benoetigt_lnr, amount) VALUES
  (5,6,12),
  (1,7,1),
  (2,7,1),
  (7,8,1);

我非常确定该解决方案仅适用于“ WITH RECURSIV”查询。我的第一次尝试是：

WITH RECURSIVE benoetigtlist(lnr,benoetigt_lnr,menge) AS (
  SELECT lnr, benoetigt_lnr, menge FROM benoetigt
    UNION ALL
  SELECT  b.lnr, b.benoetigt_lnr, b.menge
  FROM benoetigtlist bl, benoetigt b
  WHERE b.lnr = bl.benoetigt_lnr
)
SELECT * FROM benoetigtlist;

但是我不知道如何将结果与活动定价联系起来。

还是这是错误的尝试？

这是来自测试数据的SQL Fiddle，用于更好地理解：SQL Fiddle

编辑：所需的输出是一个列表，其中包含所有活动的总价格，包括其子代的价格。

对于上面的测试数据，结果应类似于：

id  text          price
1  'Discokugel'     160  // 100 + 1*40 + 1*20
2  'Strobolicht'    160  // 100 + 1*40 + 1*20
3  'Verstärker'      90
4  'Gelaender'       50
5  'Bühne'          290  // 50 + 12*20  
6  'Bühnenelement'   20
7  'Stromverteiler'  60  // 40 + 1*20
8  'Stromkabel'      20

例如，ID 5为“Bühne”的活动的总价格为290，因为该活动本身的成本为50。该活动也需要ID 6为12的十二项活动（成本为20）（根据“ benoetigt”中的第一项）。

总结：50 + 12 * 20 = 290

在作为重复标记的问题中，递归仅在一个表中发生。但是我有一个问题，价格信息在另一个表中。

用户名

现在，您需要将递归CTE的结果与活动表进行两次联接（一次联接以获取父级的价格，另一次联接以获取子级活动的价格）并按父级活动分组，以汇总子项活动的成本：

WITH RECURSIVE benoetigtlist(lnr,benoetigt_lnr,menge) AS (
  SELECT lnr, benoetigt_lnr, amount 
  FROM benoetigt
    UNION ALL
  SELECT  bl.lnr, b.benoetigt_lnr, b.amount
  FROM benoetigtlist bl
  , benoetigt b
  WHERE b.lnr = bl.benoetigt_lnr
)
SELECT parent.lnr
  , parent.bezeichnung
  -- replace null with 0 if there are no child activities
  , parent.price + COALESCE(children_cost.cost, 0) AS cost
FROM Leistung parent
-- subquery that summarizes total cost of all children:
LEFT JOIN (
  SELECT bl.lnr, SUM(bl.menge * l.price) as cost
  FROM benoetigtlist bl
  INNER JOIN Leistung l on bl.benoetigt_lnr = l.lnr
  GROUP BY bl.lnr
) children_cost ON children_cost.lnr = parent.lnr
ORDER BY parent.lnr