如何把这两张表合并？

[qq_25746045]

A表：
B表：
AB两张表都不是实体表，是查询出来的两张表，现在我想把这两张表数据合并一下，合并成这样显示在结果里面，请问该怎么写sql？

[qq_25746045]

我使用左连接的时候会出现这样的情况：

[qq_25746045]

如果使用union all的话该如何添加KYLL列呢？

[qq_25746045]

大家帮帮忙呀

[stelf]

由于你自己也不知道取哪一个编码，我就帮你去一个大的吧？ with tmp_a as (select '10' as code, '北京' as name, '908' as kyll from dual), tmp_b as (select '10' as code, '北京' as name, '780' as kyll from dual union all select '11', '河北' as name, '781' from dual) select code, name, max(kyll) as kyll from (select * from tmp_a union all select * from tmp_b) mt group by code, name;

[stelf]

join 版本，可能需要根据你的实际需求微调 with tmp_a as (select '10' as code, '北京' as name, '908' as kyll from dual), tmp_b as (select '10' as code, '北京' as name, '780' as kyll from dual union all select '11', '河北' as name, '781' from dual) select ifNULL(tmp_a.code, tmp_b.code) as code, ifNULL(tmp_a.name, tmp_b.name) as name, ifNULL(tmp_a.kyll, tmp_b.kyll) as kyll from tmp_a left join tmp_b on tmp_a.code = tmp_b.code union select ifNULL(tmp_a.code, tmp_b.code) as code, ifNULL(tmp_a.name, tmp_b.name) as name, ifNULL(tmp_a.kyll, tmp_b.kyll) as kyll from tmp_a RIGHT join tmp_b on tmp_a.code = tmp_b.code

[AHUA1001]

with tmp_a as (select '10' code, '北京' name, '908' kyll from dual), tmp_b as (select '10' code, '北京' name, '780' khll from dual union all select '11' code, '河北' name, '781' khll from dual) select tmp_b.*,ifnull(tmp_a.kyll,0) kyll from tmp_b left join tmp_a on tmp_a.code = tmp_b.code ;

[stelf]

你怎么知道是哪个表数据多，哪个数据少啊？

引用 6 楼 AHUA1001 的回复:

with tmp_a as (select '10' code, '北京' name, '908' kyll from dual), tmp_b as (select '10' code, '北京' name, '780' khll from dual union all select '11' code, '河北' name, '781' khll from dual) select tmp_b.*,ifnull(tmp_a.kyll,0) kyll from tmp_b left join tmp_a on tmp_a.code = tmp_b.code ;

[sunflowerone]

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

[hero8_8]

单从表的内容上看，应该已B表为主表left joinA表，coalesce(HYLL,0)

[AHUA1001]

引用 7 楼 stelf 的回复:

你怎么知道是哪个表数据多，哪个数据少啊？ [quote=引用 6 楼 AHUA1001 的回复:] with tmp_a as (select '10' code, '北京' name, '908' kyll from dual), tmp_b as (select '10' code, '北京' name, '780' khll from dual union all select '11' code, '河北' name, '781' khll from dual) select tmp_b.*,ifnull(tmp_a.kyll,0) kyll from tmp_b left join tmp_a on tmp_a.code = tmp_b.code ;

[/quote] 额，被误导了，这个板块是MYSQL的，MYSQL是不支持WITH的。 看楼主给的测试数据，是B表比A表数据多，我改了一下，这样应该可以。 SELECT code, name, MAX(khll) khll, MAX(kyll) kyll FROM ( SELECT code, name, NULL khll, kyll FROM a UNION ALL SELECT code, name, khll, NULL kyll FROM b ) GROUP BY code, name ORDER BY code,name;

[stelf]

mysql 8.0以后会支持with和分析函数

引用 10 楼 AHUA1001 的回复:

[quote=引用 7 楼 stelf 的回复:] 你怎么知道是哪个表数据多，哪个数据少啊？ [quote=引用 6 楼 AHUA1001 的回复:] with tmp_a as (select '10' code, '北京' name, '908' kyll from dual), tmp_b as (select '10' code, '北京' name, '780' khll from dual union all select '11' code, '河北' name, '781' khll from dual) select tmp_b.*,ifnull(tmp_a.kyll,0) kyll from tmp_b left join tmp_a on tmp_a.code = tmp_b.code ;

[/quote] 额，被误导了，这个板块是MYSQL的，MYSQL是不支持WITH的。 看楼主给的测试数据，是B表比A表数据多，我改了一下，这样应该可以。 SELECT code, name, MAX(khll) khll, MAX(kyll) kyll FROM ( SELECT code, name, NULL khll, kyll FROM a UNION ALL SELECT code, name, khll, NULL kyll FROM b ) GROUP BY code, name ORDER BY code,name;[/quote]