sql - 如何使用 UNION 用另一个表中的值填充 NULL 值

Question

我必须编写一个选择语句，从一个表中显示 orderId 和交货地址，但如果没有交货地址，它应该显示客户的帐单地址。我不确定如何执行此操作，因为 UNION 需要相同数量的列。

这是两张表：

CREATE TABLE CUSTOMER (
CustId number(4) NOT NULL,
CFirstName varchar2(30),
CSurname varchar2(30),
BillingAddr varchar2(30),
Gender varchar2(1),
PRIMARY KEY (CustId),
CONSTRAINT Check_CGender
CHECK (CGender IN ('M' , 'F'))
);

CREATE TABLE SHOPORDER (
OrdId number(4) NOT NULL,
DeliveryAddress varchar2(30),
CustId number(4),
SpId number(4),
PRIMARY KEY (OrdId),
CONSTRAINT FK_CustId
FOREIGN KEY (CustId) REFERENCES CUSTOMER,
CONSTRAINT FK_SpId
FOREIGN KEY (SpId) REFERENCES SALESPERSON
);

这就是问题的措辞：使用 UNION 子句列出每个订单 ID 和地址。该列表必须按升序排列。如果 Shop Order 收货地址为 NULL，则您必须显示客户的账单地址而不是收货地址。

任何帮助将不胜感激。

我目前的尝试哈哈

SELECT Ordid, DeliveryAddress
FROM SHOPORDER
UNION
SELECT NULL AS CustId, BillingAddr
FROM CUSTOMER;


SELECT S.OrdId, S.DeliveryAddress
FROM SHOPORDER S
UNION
SELECT S.OrdId, C.BillingAddr
FROM CUSTOMER C
INNER JOIN SHOPORDER S
ON C.CustId = S.CustId;

SELECT S.OrdId 
FROM SHOPORDER S
INNER JOIN CUSTOMER C
ON S.CustId = C.CustId;
(SELECT DeliveryAddress 
FROM SHOPORDER 
UNION 
SELECT BillingAddr 
FROM CUSTOMER );

SELECT OrdId, DeliveryAddress
FROM SHOPORDER
UNION
SELECT BillingAddr
FROM CUSTOMER;

SELECT OrdId, (SELECT DeliveryAddress
FROM SHOPORDER
UNION
SELECT BillingAddr
FROM CUSTOMER)
FROM SHOPORDER;

标签： sqlisql

Answer 1

您正在寻找LEFT JOIN和COALESCE()：

select so.OrdId, coalesce(so.DeliveryAddress, c.BillingAddr) as address
from shoporder so left join
     customer c
     on so.CustId = c.CustId;