CREATE TABLE mao(senior VARCHAR2(20),junior VARCHAR2(20),control NUMBER, broken NUMBER, youngest NUMBER);
INSERT INTO mao VALUES ('老毛','大毛',0.5,0,0);
INSERT INTO mao VALUES ('大毛','小毛',0.3,0,0);
INSERT INTO mao VALUES ('老毛','小毛',0.1,0,1);
INSERT INTO mao VALUES ('老朱','大朱',0.5,0,0);
INSERT INTO mao VALUES ('大朱','小朱',0.3,1,0);
INSERT INTO mao VALUES ('老朱','小朱',0.1,0,1);
WITH t(senior,junior,control) AS (
SELECT senior,junior,control
FROM mao
UNION ALL
SELECT t.senior,mao.junior,t.control*mao.control
FROM t,mao
WHERE t.junior = mao.senior AND mao.broken=0
)
SELECT senior,junior,SUM(control)
FROM t
GROUP BY senior,junior;
|
