SQL条件连接 [英] sql conditional join

查看:59
本文介绍了SQL条件连接的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我有3张桌子:

table1:table1_col1, table1_col2,table1_col3
table2 : table1_col1, table2_col2,table2_col3
table3 : table1_col1, table3_col2,table3_col3

正如我试图通过命名来解释的那样,table1:table1_col1是一个唯一键,可以引用table2:table1_col1或table3:table1_col1,但不能两者都引用. 我需要在table1,table2,table3之间建立联接:

As I tried to explain by the naming, table1:table1_col1 is a unique key that can reference either table2 : table1_col1 or table3 : table1_col1 but never both. I need to make a join between table1, table2,table3 such that:

join table1 with table2 if table1:table1_col1 = table2 : table1_col1
join table1 with table3 if table1:table1_col1  = table3 : table1_col1

这在sql语法中可能吗?
谢谢.

Is this possible in sql syntax?
Thank you.

推荐答案

我假设您也想要表2和3中的相应列.

I would assume you want the corresponding columns from table's 2 and 3 too.

select 
      t1.table_col1,
      t2.table2_col2,
      t2.table2_col3,
      t3.table3_col2,
      t3.table3_col3
   from
      table1 t1
         left join table2 t2
           on t1.table1_col1 = t2.table1_col1
         left join table3 t3
           on t1.table1_col1 = t3.table1_col1

此外,如果您只想要表2或表3中的列,并且它们是相同的数据类型,则可以使用NVL()函数,例如

Additionally, if you only wanted the columns from respective table 2 or 3, and they were the same data types, you could use the NVL() function, such as

select 
      t1.table_col1,
      nvl( t2.table2_col2, t3.table3_col2 ) as SecondCol,
      nvl( t2.table2_col3, t3.table3_col3 ) as ThirdCol
   from
      table1 t1
         left join table2 t2
           on t1.table1_col1 = t2.table1_col1
         left join table3 t3
           on t1.table1_col1 = t3.table1_col1

这篇关于SQL条件连接的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆