时间戳

首页 > 解决方案 > 避免 MSSQL 中的多个左连接

sql-server - 避免 MSSQL 中的多个左连接

问题描述

我有以下数据库结构：

Users
----------------------
| User_ID | Username |
|--------------------|
|  14590  | Sam      |
|  14591  | Michael  |
|  14592  | Albert   |
----------------------

Addresses
----------------------------------------------
| Adr_ID | City      | Street                |
|--------------------------------------------|
|   62   | New York  | Perfect Street 1      | 
|   63   | New York  | Another Street 12     |
|   64   | Prague    | Zlata Ulicka 52       |
|   65   | Berlin    | Alexanderplatz 36     |
|   66   | Berlin    | Am Bahnhof 49         |
|   67   | Warsaw    | Poniatowskiego 74     |
|   68   | Paris     | Rue Des Barres 33     |
|   69   | Paris     | Rue De L’abreuvoir 63 |
|   70   | Lisbon    | Rua Augusta           |
----------------------------------------------

Addresses_Link
------------------------------------------------------------
| Link_ID | Adr_ID | User_ID | Main_Address | Address_Type |
|----------------------------------------------------------|
|   570   |   62   |  14590  |       1      |       1      |
|   571   |   63   |  14590  |       1      |       2      |
|   572   |   64   |  14590  |       0      |       3      |
|   573   |   65   |  14591  |       1      |       1      |
|   574   |   66   |  14591  |       1      |       2      |
|   575   |   67   |  14591  |       0      |       3      |
|   576   |   68   |  14592  |       1      |       1      |
|   577   |   69   |  14592  |       1      |       2      |
|   578   |   70   |  14592  |       0      |       3      |
------------------------------------------------------------

我想得到的结果：

-----------------------------------------------------------------------------------------------------
| User_ID | Username | Adr_Private_City | Adr_Private_Street | Adr_Job_City | Adr_Job_Street        |
|---------------------------------------------------------------------------------------------------|
| 14590   | Sam      | New York         | Perfect Street 1   | New York     | Another Street 12     |
| 14591   | Michael  | Berlin           | Alexanderplatz 36  | Berlin       | Am Bahnhof 49         |
| 14592   | Albert   | Paris            | Rue Des Barres 33  | Paris        | Rue De L’abreuvoir 63 |
-----------------------------------------------------------------------------------------------------

列：

Adr_Private_City / Adr_Private_Street - 当 Main_Address = 1 和 Address_Type = 1 时

Adr_Job_City / Adr_Job_Street - 当 Main_Address = 1 和 Address_Type = 2

我创建了一个这样的 SQL 查询：

SELECT 
u.User_ID,
u.Username,
a1.City AS Adr_Private_City,
a1.Street AS Adr_Private_Street,
a2.City AS Adr_Job_City,
a2.Street AS Adr_Job_Street

FROM Users u
LEFT JOIN Addresses_Link al1 ON al1.User_ID = u.User_ID
LEFT JOIN Addresses_Link al2 ON al2.User_ID = u.User_ID
LEFT JOIN Addresses a1 ON a1.Adr_ID = al1.Adr_ID
LEFT JOIN Addresses a2 ON a2.Adr_ID = al2.Adr_ID

WHERE 
al1.Main_Address = 1 AND al1.Address_Type = 1 AND 
al2.Main_Address = 1 AND al2.Address_Type = 2

是否可以避免多个左连接并使查询不会太慢？

标签： sql-server

解决方案

您可以通过以下方式实现您想要的

您的原始查询

FROM Users u
LEFT JOIN Addresses_Link al1 ON al1.User_ID = u.User_ID
....
WHERE al1.Main_Address = 1

当您在子句INNER JOIN中有条件时，实际上是一个al1.Main_Address = 1WHERE

既然你用过LEFT JOIN，我就把它变成了一个真正的LEFT JOIN查询。因为Addresses_Linkand Addresses，既然你加入了Adr_ID，我用INNER JOIN

SELECT *
FROM   Users u
       LEFT JOIN
       (
         SELECT al.User_ID,
                MAX(CASE WHEN al.Address_Type = 1 THEN a.City END) AS Adr_Private_City,
                MAX(CASE WHEN al.Address_Type = 1 THEN a.Street END) AS Adr_Private_Street,
                MAX(CASE WHEN al.Address_Type = 2 THEN a.City END) AS Adr_Job_City,
                MAX(CASE WHEN al.Address_Type = 2 THEN a.Street END) AS Adr_Job_Street,
         FROM   Addresses_Link al
                INNER JOIN Addresses a ON a.Adr_ID = al.Adr_ID
         WHERE  al.Main_Address  = 1
         AND    al.Address_Type  IN (1, 2)
         GROUP BY al.User_ID
       ) a    ON a.User_ID  = u.User_ID

推荐阅读