sql—仅从两个联接表中选择一个结果字段

31moq8wy  于 2021-08-13  发布在  Java
关注(0)|答案(1)|浏览(384)

我试图从两个表中只选择一个字段。而不是 c.cust_name 以及 s.supp_name 其中只有一个字段名 customer 或者 supplier table。

SELECT o.id, o.order_nr, o.cust_suppl, c.cust_name, s.supp_name
FROM   Orders AS o LEFT OUTER JOIN
       customer AS c ON o.id = c.id LEFT OUTER JOIN
       supplier AS s ON o.id = s.id

电流输出:

id    order_nr   cust_suppl   cust_name   supp_name
1       1100       c            Cust_A      NULL    
2       1101       c            Cust_B      NULL
3       2100       s            NULL        Suppl_A
4       2101       s            NULL        Suppl_B
yi0zb3m4

yi0zb3m41#

你可以解码:

SELECT o.id, o.order_nr, o.cust_suppl
       , decode( c.cust_name, null, s.supp_name, c.cust_name )
FROM   Orders AS o LEFT OUTER JOIN
       customer AS c ON o.id = c.id LEFT OUTER JOIN
       supplier AS s ON o.id = s.id

或使用案例:

SELECT o.id, o.order_nr, o.cust_suppl
       , case when c.cust_name is null then s.supp_name else c.cust_name end
FROM   Orders AS o LEFT OUTER JOIN
       customer AS c ON o.id = c.id LEFT OUTER JOIN
       supplier AS s ON o.id = s.id

相关问题