A表：
B表：
AB兩張表都不是物體表，是查詢出來的兩張表，現在我想把這兩張表資料合并一下，合并成這樣
顯示在結果里面，請問該怎么寫sql？
uj5u.com熱心網友回復：
我使用左連接的時候會出現這樣的情況：
uj5u.com熱心網友回復：
如果使用union all的話該如何添加KYLL列呢？uj5u.com熱心網友回復：
大家幫幫忙呀
uj5u.com熱心網友回復：
由于你自己也不知道取哪一個編碼，我就幫你去一個大的吧？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;
uj5u.com熱心網友回復：
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
uj5u.com熱心網友回復：
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 ;
uj5u.com熱心網友回復：
你怎么知道是哪個表資料多，哪個資料少啊？
uj5u.com熱心網友回復：
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;uj5u.com熱心網友回復：
單從表的內容上看，應該已B表為主表left joinA表，coalesce(HYLL,0)uj5u.com熱心網友回復：
額，被誤導了，這個板塊是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;
uj5u.com熱心網友回復：
mysql 8.0以后會支持with和分析函式轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/49908.html
標籤：MySQL
下一篇：hive遇到一個問題