The result of the recursive query statement is not what I want

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

alt text

Use recursive query and total quantity：

alt text

sqla

asked 10 Jul '21, 06:35

mfkpie8
238●64●69●72
accept rate: 12%

Maybe the second example in this blog post will help: Example: RECURSIVE UNION.

(10 Jul '21, 07:02) Breck Carter

It looks like mine is a counterclockwise recursion, I can't get it out!Please help me look at my problem

(10 Jul '21, 10:40) mfkpie8

flat view

One Answer:

active answers oldest answers newest answers popular answers

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

permanent link

answered 10 Jul '21, 12:28

mfkpie8
238●64●69●72
accept rate: 12%

edited 10 Jul '21, 12:29

flat view

Your answer

toggle preview

community wiki:

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

*italic* or _italic_
**bold** or __bold__
link:[text](http://url.com/ "title")
image?![alt text](/path/img.jpg "title")
numbered list: 1. Foo 2. Bar
to add a line break simply add two spaces to where you would like the new line to be.
basic HTML tags are also supported

learn more about Markdown

Question tags:

sqla ×25

question asked: 10 Jul '21, 06:35

question was seen: 579 times

last updated: 10 Jul '21, 12:29

SAP SQL Anywhere

The result of the recursive query statement is not what I want

Follow this question

Related questions