两个表与条件相结合的结果

Question

我有两张表，我正试着把它们的结果结合起来。

Create table dbo.streetaddr1(HomeID INT,Address varchar(200));

INSERT INTO dbo.streetaddr1 VALUES(1, '656 ave.');
INSERT INTO dbo.streetaddr1 VALUES(2,'B-6 ');
INSERT INTO dbo.streetaddr1 VALUES(3,'13 villa ');




Create table dbo.streetaddr2(HomeID INT,Address varchar(200));
   INSERT INTO dbo.streetaddr2 VALUES(1, '656 ave.');
   INSERT INTO dbo.streetaddr2 VALUES(2,'B-6 6th avene');
   INSERT INTO dbo.streetaddr2 VALUES(4,'25 Main street');
   INSERT INTO dbo.streetaddr2 VALUES(5,'135 Elm St ');

如果HomeID存在于dbo.streetaddr1中，那么我们从它中选择地址，即使它也存在于dbo.streetaddr2中，我们也不选择它。如果HomeID在dbo.streetaddr1中不存在，那么我们从dbo.streetaddr2中选择这些地址。

预期产出表如下：

Create table dbo.outputtable(HomeID INT,Address varchar(200));
   INSERT INTO dbo.outputtable VALUES(1, '656 ave.');
   INSERT INTO dbo.outputtable VALUES(2,'B-6 ');
   INSERT INTO dbo.outputtable VALUES(3,'13 villa ');
   INSERT INTO dbo.outputtable VALUES(4,'25 Main street');
   INSERT INTO dbo.outputtable VALUES(5,'135 Elm St ');

我怎么能这么做？

谢谢Rs

Answer 1

尝尝这个

SELECT COALESCE(S1.HOMEID,S2.HOMEID) AS HOMEID,
       COALESCE(S1.ADDRESS,S2.ADDRESS) AS ADDRESS
FROM streetaddr1 S1 
        full join streetaddr2 s2 on s1.HomeId = s2.HomeId