SQL Server - 条件左连接，两边都有条件

Question

我正在尝试使用条件连接执行 SELECT 查询，但是连接的两侧都有条件，因为根据是否满足条件，连接的表和列将会不同。这是我正在尝试的查询:

SELECT   per.Name, post.Category 
FROM     Person per
         LEFT JOIN WorkAddress wa ON per.WorkAddressID = wa.ID
         LEFT JOIN HomeAddress ha ON per.ID = ha.PersonID
         LEFT JOIN Postcode post ON 
         (CASE   WHEN per.WorkAddressID IS NOT NULL THEN wa.PostCodeID ELSE ha.PostCode+ha.Suburb END) =
         (CASE WHEN per.WorkAddressID IS NOT NULL THEN post.ID ELSE post.PostcodeSuburb END)

这是我正在使用的数据:

邮政编码:

[ID] INT IDENTITY (1,1) NOT NULL CONSTRAINT [PK_Postcode] PRIMARY KEY,
[Postcode] VARCHAR(4) NOT NULL,
[PostcodeSuburb] VARCHAR(100) NOT NULL,
[Category] INT NOT NULL

+----+----------+----------------+----------+
| ID | Postcode | PostcodeSuburb | Category |
+----+----------+----------------+----------+
|  1 |     1000 | 1000CityA      |        1 |
|  2 |     2000 | 2000CityB      |        2 |
+----+----------+----------------+----------+

工作地址:

[ID] INT IDENTITY (1,1) NOT NULL CONSTRAINT [PK_WorkAddress] PRIMARY KEY,
[Name] VARCHAR(50) NOT NULL,
[Address] VARCHAR(100) NOT NULL,
[PostCodeID] INT NOT NULL CONSTRAINT [FK_WorkAddress_PostCodeID] FOREIGN KEY REFERENCES Postcode(ID)

+----+-----------------+---------------+------------+
| ID |      Name       |    Address    | PostcodeID |
+----+-----------------+---------------+------------+
|  1 | CityA Town Hall | 10 Main Road  |          1 |
|  2 | CityB Palace    | 1 Palace Lane |          2 |
+----+-----------------+---------------+------------+

人:

[ID] INT IDENTITY (1,1) NOT NULL CONSTRAINT [PK_Person] PRIMARY KEY,
[Name] VARCHAR(50) NOT NULL,
[WorkAddressID] INT NULL CONSTRAINT [FK_Person_WorkAddressID] FOREIGN KEY REFERENCES WorkAddress(ID)

+----+---------------+---------------+
| ID |     Name      | WorkAddressID |
+----+---------------+---------------+
|  1 | Johnny Smiles | 1             |
|  2 | Granny Smith  | NULL          |
|  3 | Smithee Black | 2             |
+----+---------------+---------------+

家庭地址:

[ID] INT IDENTITY (1,1) NOT NULL CONSTRAINT [PK_HomeAddress] PRIMARY KEY,
[PersonID] INT NOT NULL CONSTRAINT [FK_HomeAddress_PersonID] FOREIGN KEY REFERENCES Person(ID),
[Address] VARCHAR(100) NOT NULL,
[PostCode] VARCHAR(4) NOT NULL,
[Suburb] VARCHAR(50) NOT NULL

+----+----------+----------------+----------+--------+
| ID | PersonID |    Address     | PostCode | Suburb |
+----+----------+----------------+----------+--------+
|  1 |        1 | 3 Little Road  |     1000 | CityA  |
|  2 |        2 | 80 Main Road   |     1000 | CityA  |
|  3 |        3 | 6 Village Lane |     2000 | CityB  |
+----+----------+----------------+----------+--------+

目前，我收到错误将 varchar 值“2000CityB”转换为数据类型 int 时转换失败。尽管两个 CASE 语句中使用了相同的条件，但还是出现了这种情况。我想知道这实际上是否可以通过 CASE 语句来完成，或者我是否需要使用不同的方法，因为如果只有一侧有 CASE 语句，而不是两侧都有 CASE 语句，则带有 CASE 的条件 JOIN 确实可以工作。

不，我不想完全改变表结构。

SQL fiddle :http://www.sqlfiddle.com/#!18/56485/4

Answer 1

我相信您只需将 int 转换为 varchar

SELECT    per.Name, post.Category 
FROM      Person per
          LEFT JOIN WorkAddress wa ON per.WorkAddressID = wa.ID
          LEFT JOIN HomeAddress ha ON per.ID = ha.PersonID
          LEFT JOIN Postcode post ON 
          (CASE WHEN per.WorkAddressID IS NOT NULL THEN CAST(wa.PostCodeID AS varchar(100)) ELSE ha.PostCode+ha.Suburb END) =
          (CASE WHEN per.WorkAddressID IS NOT NULL THEN CAST(post.ID AS varchar(100)) ELSE post.PostcodeSuburb END)

sqlfiddle

在这种情况下，问题似乎是由于 SQL Server 始终执行从 varchar 到 int 的隐式转换(如果比较 int 和 varchar)而引起的。您可以通过以下示例进行尝试:

-- #1 example
with data as
(
 select 10 a, '10' b
)
select * from data where a = b;

-- #2 example
with data as
(
 select 10 a, 'b' b
)
select * from data where a = b;

dbfiddle

编辑:正如@dnoeth提到的，CASE要求所有返回路径返回相同的数据类型，因此SQL Server由于而进行varchar转换int 优先级(如上例所示)。