The create table aa (zm varchar2 (10), pk number (4));
The create table bb (zm varchar2 (10), railway number (4));
Insert into aa values (' Beijing ', 111);
Insert into aa values (' shenzhen ', 222);
Insert into aa values (' Beijing ', 333);
Insert into aa values (' Shanghai ', 444);

Insert into bb values (' Beijing ', 555);
Insert into bb values (' shenzhen ', 666);
Insert into bb values (' Beijing ', 111);
Insert into bb values (' shenzhen ', 222);


Want to query result:
Zm aa. Pk bb. Railway
Beijing, 444666
Shenzhen, 222888
Shanghai, 444, 0

CodePudding user response:

Help to solve it, thanks

CodePudding user response:

The select zm, sum (pka), sum (PKB)
The from (
The select zm, sum (pk) as pka, 0 as pka from aa group by zm
Union all
The select zm, 0 as pka, sum (pk) as PKB from bb group by zm
)
Group by zm

- it's just a writing, may not be optimal, but certainly not the worst

The worst is, two tables, respectively, after doing FULL JOIN,