|
I want to use recursive query to achieve the total number, but the existing statement can only count the ID of the first layer: CREATE TABLE "DBA"."sumall" (
"ID" CHAR(5) NOT NULL,
"NUM_one" NUMERIC(6,2) NULL,
"NUM_TWO" NUMERIC(6,2) NULL,
"parent_ID" CHAR(5) NULL,
CONSTRAINT "ID" PRIMARY KEY ( "ID" ASC )
) IN "system";
COMMENT ON TABLE "DBA"."sumall" IS '
';
INSERT INTO "DBA"."sumall" ("ID","NUM_one","NUM_TWO","parent_ID") VALUES('1011',2.00,11.00,NULL);
INSERT INTO "DBA"."sumall" ("ID","NUM_one","NUM_TWO","parent_ID") VALUES('1021',2.00,33.00,NULL);
INSERT INTO "DBA"."sumall" ("ID","NUM_one","NUM_TWO","parent_ID") VALUES('1033',23.00,332.00,'1021');
INSERT INTO "DBA"."sumall" ("ID","NUM_one","NUM_TWO","parent_ID") VALUES('1044',2.00,2.00,'1011');
INSERT INTO "DBA"."sumall" ("ID","NUM_one","NUM_TWO","parent_ID") VALUES('10881',23.00,2333.00,'1033');
INSERT INTO "DBA"."sumall" ("ID","NUM_one","NUM_TWO","parent_ID") VALUES('4566',1.00,2.00,'1033');
INSERT INTO "DBA"."sumall" ("ID","NUM_one","NUM_TWO","parent_ID") VALUES('7788',2.00,1.00,'4566');
SQL: WITH RECURSIVE sumid (d1,dd,sum_one,sum_two,) as ( select id as d1,id as dd , num_one , num_two two from sumall where sumall.id in (select DISTINCT parent_id from sumall where isnull(parent_id,'') <> '') union all select sumid.dd,sumall.id,num_one ,num_two from sumall,sumid where sumid.dd=sumall.parent_id) select * from sumid
Use recursive query and total quantity：
|
|
The answer is as follows： WITH RECURSIVE sumid (d1,dd,sum_one,sum_two,)
as (
select id as d1,id dd , num_one , num_two two from sumall where sumall.id in (select DISTINCT parent_id from sumall where isnull(parent_id,'') <> '')
union all
select sumall.ID,sumid.dd,num_one ,num_two from sumall,sumid where sumid.d1=sumall.parent_id)
select dd,sum(sum_one),sum(sum_two) from sumid group by dd
Thanks for Breck Carter and sybase forum result: dd,sum(sumid.sum_one),sum(sumid.sum_two) '1011',4.000000,13.000000 '1021',51.000000,2701.000000 '1033',49.000000,2668.000000 '4566',3.000000,3.000000 |
Maybe the second example in this blog post will help: Example: RECURSIVE UNION.
It looks like mine is a counterclockwise recursion, I can't get it out!Please help me look at my problem