英文:
How to change the join columns based on Condition
问题
如何根据列值更改连接列，如果列A具有值，则连接条件应为t1.a = t2.a，如果A为空值，则t1.b = t2.b，如果两列都有值，则需要添加t1.a = t2.a和t1.b = t2.b，请建议。
英文:
How to change the join columns based on the column value , If the column A has value, then the join condition should be t1.a= t2.a, if the A is null value then t1.b=t2.b if both column has values then need to add t1.a= t2.a and t1.b=t2.b, Please advice
答案1
得分: 0
使用COALESCE()处理空值的示例数据如下：
select *
from table1 t1 inner join table2 t2
on t2.A = coalesce(t1.A, t2.A) and t2.B = coalesce(t1.B, t2.B)
查看演示。<br/>
结果：
> A | B | A | B
> ---: | ---: | -: | -:
> 4 | null | 4 | 2
> null | 5 | 5 | 5
> 2 | 2 | 2 | 2
英文:
For this sample data use COALESCE() to deal with nulls:
select *
from table1 t1 inner join table2 t2
on t2.A = coalesce(t1.A, t2.A) and t2.B = coalesce(t1.B, t2.B)
See the demo.<br/>
Results:
> A | B | A | B
> ---: | ---: | -: | -:
> 4 | null | 4 | 2
> null | 5 | 5 | 5
> 2 | 2 | 2 | 2
答案2
得分: 0
你可以尝试类似这样的方法：
select *
from Table1 t1, Table2 t2
where (t1.a is not null and t1.b is not null and t1.a = t2.a and t1.b = t2.b) or
(t1.a is null and t1.b is not null and t1.b = t2.b) or
(t1.a is not null and t1.b is null and t1.b = t2.b)
英文:
You can try something like this:
select *
from Table1 t1, Table2 t2
where (t1.a is not null and t1.b is not null and t1.a = t2.a and t1.b = t2.b) or
(t1.a is null and t1.b is not null and t1.b = t2.b) or
(t1.a is not null and t1.b is null and t1.b = t2.b)
答案3
得分: 0
尝试以下查询：
select * from TABLE1
inner join TABLE2
on TABLE1.A=TABLE2.A
or (select TABLE1.B from TABLE1 where TABLE1.A is null)=TABLE2.B
输出：
A B A_1 B_1
---- ---- ---- ---
4 NULL 4 2
NULL 5 NULL 5
2 2 2 2
英文:
Try Below Query
select * from TABLE1
inner join TABLE2
on TABLE1.A=TABLE2.A
or (select TABLE1.B from TABLE1 where TABLE1.A is null)=TABLE2.B
Output
A B A_1 B_1
---- ---- ---- ---
4 NULL 4 2
NULL 5 NULL 5
2 2 2 2
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论