当前位置：网站首页>如何SQL 语句UNION实现当一个表中的一列内容为空时则取另一个表的另一列

如何SQL 语句UNION实现当一个表中的一列内容为空时则取另一个表的另一列

2022-04-23 06:23:00 【CSDN问答】

问题遇到的现象和发生背景

问题相关代码，请勿粘贴截图

以下代码为SQL server 中生成数据表内容的代码，供测试使用
create table customer
(
custid int primary key,
cfirstname char(30),
csurname char(30),
billingaddress char(30),
cgender char(1) check(cgender in('M','F'))
);
create table shoporder
(
ordid int primary key,
deliveryaddress char(30),
spid int,
custid int,
foreign key(spid) references salesperson(spid),
foreign key(custid) references customer(custid)
);
use "3120004023";
INSERT
INTO customer(custid,cfirstname,csurname,cgender,billingaddress)
VALUES
('1','Casey','Cartwright','F','1 Roma Ave Cranbourne'),
('2','Evan','Chambers','M','8 David St Dandenong'),
('3','Calvin','Owens','M','7 Long Rd Lara'),
('4','Frannie','Morgan','F','9 Down Pde Upwey'),
('5','Cappie','Jones','M','6 Mist St Toorak'),
('6','Dana','Stockwell','F','2 Tree St Epping'),
('7','Ash','Howard','F','4 Elm Ave Elwood');
use "3120004023";

INSERT
INTO shoporder(ordid, deliveryaddress,spid,custid)
VALUES
(41,NULL,23,1),
(51,NULL,23,1),
(42,NULL,21,2),
(43,NULL,23,2),
(49,NULL,24,2),
(44,'1 John St Hawthorn',26,3),
(48,NULL,26,3),
(45,'1254 Dunstall Rd Coorparoo',22,4),
(47,'727 Hudson Rd Glenorchy',26,4),
(50,'517 Franklin St Dowerin',22,4),
(46,NULL,21,6);

我的解答思路和尝试过的方法

尝试过使用case when 达到相同的效果，但题目要求是使用 union子句
select ordid,case when deliveryaddress is NULL then billingaddress else deliveryaddress end as address from shoporder,customer where shoporder.custid=customer.custid;