我有兩個查詢結果：
id | col1 | col2 | col3
1 1 null 3j
2 2 12 35
3 null 32 31
4 null 43 33
5 null 44 4
id | col1 | col2 | col3
6 1 null 3j
7 2 null 35
8 3 null 31
9 4 null 33
10 5 null null
我需要做聯合：
id | col1 | col2 | col3
6 1 null 3j
7 2 12 35
8 3 32 31
9 4 43 33
10 5 null null
5 null 44 4
問題是缺少一些值
我寫了這個大 sql 查詢來解決這個問題：
select *
from (
select max(id) as id,
max(col1) as col1,
max(col2) as col2,
max(col3) as col3
from (
select max(id) as id,
max(col1) as col1,
max(col2) as col2,
max(col3) as col3
from (
select max(id) as id,
max(col1) as col1,
max(col2) as col2,
max(col3) as col3
from (
select *
from t1
where id = 1
union
select *
from t2
where id = 2
) t
group by case
when col1 is null
or
length(col1) =
0 then id
else col1 end
) t1
group by case
when col2 is null
or length(col2) = 0
then id
else col2 end
) t2
group by case
when col3 is null
or length(col3) = 0 then id
else col3 end
) t3
可能有一些簡化它的想法嗎？或者有沒有其他方法可以有效地豐富資料，因為我還需要做交叉，右，左，內并，我不想構建這樣的怪物查詢
uj5u.com熱心網友回復：
好吧，你的貓嘗試這樣的事情：聯合
select max(col1),
max(col2),
max(col3)
from t1
where id = 1
or id = 2
group by coalesce(nullif(col1, ''),
nullif(col2, ''),
nullif(col3, ''));
upd：外部聯合
select max(col1),
max(col2),
max(col3)
from t1
where id = 1
or id = 2
group by coalesce(nullif(col1, ''),
nullif(col2, ''),
nullif(col3, ''))
having count = 1;
行內
select max(col1),
max(col2),
max(col3)
from t1
where id = 1
or id = 2
group by coalesce(nullif(col1, ''),
nullif(col2, ''),
nullif(col3, ''))
having count > 1;
left 和 right 與帶有 'where' 的常見查詢外部相交
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/426891.html
標籤：sql PostgreSQL 联盟 路口
上一篇：通過非空值進行SQL連接