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