A table:
Table B:
AB table, two tables are not entities is query out two tables, now I want to combine the two table data, merge into this is shown in the result, could you tell me how to write the SQL?

CodePudding user response:

When I use the left link will appear such circumstance:

CodePudding user response:

If using a union all how to add KYLL column?

CodePudding user response:

everybody to help

CodePudding user response:

Because you don't know which one coding, I will help you to go to a big?

With tmp_a as
(select the '10' as code, as the name 'Beijing', '908' as kyll from dual),
Tmp_b as
(select the '10' as code, as the name 'Beijing', '780' as kyll
The from dual
Union all
Select '11', 'hebei' as the name, '781'
The from dual)
Select code, name, Max (kyll) as kyll
The from (select * from tmp_a union all select * from tmp_b) mt
Group by code, the name;

CodePudding user response:

Join version, may need to be according to your actual demand for fine-tuning

With tmp_a as
(select the '10' as code, as the name 'Beijing', '908' as kyll from dual),
Tmp_b as
(select the '10' as code, as the name 'Beijing', '780' as kyll
The from dual
Union all
Select '11', 'hebei' as the name, '781'
The from dual)
The select ifNULL (tmp_a. Code, tmp_b code) as code,
IfNULL (tmp_a. Name, tmp_b. Name) as the name,
IfNULL (tmp_a kyll, tmp_b kyll) as kyll

The from tmp_a
Left the join tmp_b
On tmp_a. Code=tmp_b. Code
The union
The select ifNULL (tmp_a. Code, tmp_b code) as code,
IfNULL (tmp_a. Name, tmp_b. Name) as the name,
IfNULL (tmp_a kyll, tmp_b kyll) as kyll
The from tmp_a
RIGHT join tmp_b
On tmp_a. Code=tmp_b. Code

CodePudding user response:

With tmp_a as
(select the '10' code, the name 'Beijing', '908' kyll from dual),
Tmp_b as
(select the '10' code, the name 'Beijing', '780' KHLL from dual
Union all
Select '11' code, 'hebei' name, '781' KHLL from dual)
The select tmp_b. *, ifnull (tmp_a kyll, 0) kyll from tmp_b left join tmp_a on tmp_a. Code=tmp_b. Code;

CodePudding user response:

How do you know which table data is much, which less data?

refer to 6th floor AHUA1001 response:

with tmp_a as
(select the '10' code, the name 'Beijing', '908' kyll from dual),
Tmp_b as
(select the '10' code, the name 'Beijing', '780' KHLL from dual
Union all
Select '11' code, 'hebei' name, '781' KHLL from dual)
The select tmp_b. *, ifnull (tmp_a kyll, 0) kyll from tmp_b left join tmp_a on tmp_a. Code=tmp_b. Code;

CodePudding user response:

B. select * from (select *, @ rn: + 1 r1=@ rn from (select * from b. b, b1 (select @ rn: r1=0) order by code) b) b1 left join (select *, b. @ rn:=@ rn r1 + 1 from (select a. * from a, a1 (select @ rn:=0 r) order by code) b) a1 on a1. R1=b1. R1.

CodePudding user response:

Single point of view, from the content of the table should be given priority has B table table left joinA table, coalesce (HYLL, 0)

CodePudding user response:

The

reference 7 floor stelf response:

how do you know which table data is much, less which data?

Quote: refer to the sixth floor AHUA1001 response:

With tmp_a as
(select the '10' code, the name 'Beijing', '908' kyll from dual),
Tmp_b as
(select the '10' code, the name 'Beijing', '780' KHLL from dual
Union all
Select '11' code, 'hebei' name, '781' KHLL from dual)
The select tmp_b. *, ifnull (tmp_a kyll, 0) kyll from tmp_b left join tmp_a on tmp_a. Code=tmp_b. Code;

Well, being misled, the plate is MYSQL, MYSQL is not support WITH,
Look at the building Lord to the test data, table B is more than A table data, I changed, so should be able to,
SELECT
Code,
The name,
MAX (KHLL) KHLL,
MAX (kyll) kyll
The FROM
(
SELECT
Code,
The name,
NULL KHLL,
Kyll
The FROM
A
UNION ALL
SELECT
Code,
The name,
KHLL,
NULL kyll
The FROM
B
)
GROUP BY
Code,
The name
The ORDER BY code, the name;

CodePudding user response:

Mysql 8.0 will support with and analysis function

references to the tenth floor AHUA1001 response:

Quote: refer to 7th floor stelf response:

How do you know which table data is much, which less data?

Quote: refer to the sixth floor AHUA1001 response:

With tmp_a as
(select the '10' code, the name 'Beijing', '908' kyll from dual),
Tmp_b as
(select the '10' code, the name 'Beijing', '780' KHLL from dual
Union all
nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull