首页 文章

SQL查询，连接两个表

提问于
浏览 次
1

我需要连接来自两个不同表的数据，如下所示 . 只有一个SQL查询可以吗？如果"key"和"name"在两个表上，那么它们是相同的 . "Status"在table1上始终为"-"，在table2上始终为"T" . 两个表上的"name"和"comp"也匹配（例如：Name3-C和Name4-B） .
我曾试图用"union"和"join"做但没有解决方案！

表格1

t1.name     t1.time   t1.comp   t1.key   t1.status
name1        1          B         106        -
name2        2          B         -          -
name3        1          C         102        -
name4        3          B         103        -
name7        1          C         104        -

表2

t2.name     t2.time   t2.comp   t2.key    t2.status
name5        6          B         100        T
name6        5          B         -          T
name3        7          C         102        T
name4        9          B         103        T

结果应该是......

name      time1    time2   t.comp   t.key   t.status
name1        1       -       B       106       -  
name2        2       -       B        -        -
name3        1       7       C       102       T
name4        3       9       B       103       T
name5        -       6       C       100       T
name6        -       5       B        -        T
name7        1       -       C       104       -

提前致谢！

sql join union

3 回答

  • 3

    如果我理解正确，你想要一个 full outer join ：

    select coalesce(t1.name, t2.name) as name,
       t1.time as time1, t2.time as time2,
       coalesce(t1.comp, t2.comp) as comp,
       coalesce(t1.key, t2.key) as key,
       t2.status
from table1 t1 full outer join
     table2 t2
     on t1.key = t2.key and t1.name = t2.name;
    回复于
  • 0
    Select * from table1
union all 
Select * from table2

    假设表的模式是相同的 . 如果他们不只是将字段从一个表添加到另一个表 .

    Select field1,field2,NULL,NULL from table1
union all 
Select NULL,NULL,field3,field4 from table2
    回复于
  • 0
    SELECT 
  T1.time AS Time1,
  T2.Time AS Time2,
  ISNULL(T1.COMP,T2.Comp) AS Comp,
  ISNULL(T1.Key,T2.Key)
FROM TABLE1 T1
FULL OUTER JOIN TABLE2 T2
ON T1.key = T2.key
    回复于

相关问题