나는 테이블 'Leistung'(영어로 활동을 의미)과 주어진 양의 두 활동 사이의 일치 (benoetigt)를위한 테이블을 가지고 있습니다. 예를 들어 ID가 2 인 활동에는 ID가 3 인 활동이 3 개 필요합니다.
각 활동 (leistung)에는 가격이 있습니다.
이제 적절한 금액의 아동 활동 가격을 포함하여 모든 활동 (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;
그러나 나는이 결과와 활동의 가격 사이를 어떻게 연결할 수 있는지 모르겠습니다.
아니면 이것은 잘못된 시도입니까?
다음은 더 나은 이해를 위해 testdata의 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 (20 개 비용)의 12 개 활동이 필요합니다 ( '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
이 기사는 인터넷에서 수집됩니다. 재 인쇄 할 때 출처를 알려주십시오.
침해가 발생한 경우 연락 주시기 바랍니다[email protected] 삭제
몇 마디 만하겠습니다