由于你自己也不知道取哪一个编码,我就帮你去一个大的吧?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;
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
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 ;
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;
单从表的内容上看,应该已B表为主表left joinA表,coalesce(HYLL,0)
额,被误导了,这个板块是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;
mysql 8.0以后会支持with和分析函数 额,被误导了,这个板块是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;
(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;
(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
(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 ;
额,被误导了,这个板块是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;
额,被误导了,这个板块是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;