如何将 LEFT JOIN 限制为 SQL Server 中的第一个结果？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3375436/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I limit a LEFT JOIN to the 1st result in SQL Server?
提问by Justin808
I have a bit of SQL that is almost doing what I want it to do. I'm working with three tables, a Users, UserPhoneNumbers and UserPhoneNumberTypes. I'm trying to get a list of users with their phone numbers for an export.
我有一些 SQL 几乎可以做我想要它做的事情。我正在处理三个表，一个用户、UserPhoneNumbers 和 UserPhoneNumberTypes。我正在尝试获取包含电话号码的用户列表以进行导出。
The database itself is old and has some integrity issues. My issue is that there should only ever be 1 type of each phone number in the database but thats not the case. When I run this I get multi-line results for each person if they contain, for example, two "Home" numbers.
数据库本身很旧并且存在一些完整性问题。我的问题是数据库中每个电话号码应该只有一种类型，但事实并非如此。当我运行它时，如果每个人包含两个“家庭”号码，我会得到每个人的多行结果。
How can I modify the SQL to take the first phone number listed and ignore the remaining numbers? I'm in SQL Server and I know about the TOP statement. But if I add 'TOP 1' to the LEFT JOIN select statement its just giving me the 1st entry in the database, not the 1st entry for each User.
如何修改 SQL 以获取列出的第一个电话号码并忽略其余号码？我在 SQL Server 中，我知道 TOP 语句。但是如果我将“TOP 1”添加到 LEFT JOIN select 语句中，它只会给我数据库中的第一个条目，而不是每个用户的第一个条目。
This is for SQL Server 2000.
这适用于 SQL Server 2000。
Thanks,
谢谢，
SELECT Users.UserID,
Users.FirstName, Users.LastName,
HomePhone, WorkPhone, FaxNumber
FROM Users
LEFT JOIN
(SELECT UserID, PhoneNumber AS HomePhone
FROM UserPhoneNumbers LEFT JOIN UserPhoneNumberTypes ON UserPhoneNumbers.UserPhoneNumberTypeID=UserPhoneNumberTypes.UserPhoneNumberTypeID
WHERE UserPhoneNumberTypes.PhoneNumberType='Home') AS tmpHomePhone
ON tmpHomePhone.UserID = Users.UserID
LEFT JOIN
(SELECT UserID, PhoneNumber AS WorkPhone
FROM UserPhoneNumbers LEFT JOIN UserPhoneNumberTypes ON UserPhoneNumbers.UserPhoneNumberTypeID=UserPhoneNumberTypes.UserPhoneNumberTypeID
WHERE UserPhoneNumberTypes.PhoneNumberType='Work') AS tmpWorkPhone
ON tmpWorkPhone.UserID = Users.UserID
LEFT JOIN
(SELECT UserID, PhoneNumber AS FaxNumber
FROM UserPhoneNumbers LEFT JOIN UserPhoneNumberTypes ON UserPhoneNumbers.UserPhoneNumberTypeID=UserPhoneNumberTypes.UserPhoneNumberTypeID
WHERE UserPhoneNumberTypes.PhoneNumberType='Fax') AS tmpFaxNumber
ON tmpFaxNumber.UserID = Users.UserID
采纳答案by Dan J
Since it's SQL Server 2000 and ranking functions are out, you could make your subquery SELECTs aggregate:
由于它是 SQL Server 2000 并且排名函数已失效，您可以使您的子查询 SELECTs 聚合：
SELECT UserID, MAX(PhoneNumber) AS HomePhone FROM [...] GROUP BY UserID
iff you don't care WHICH of a user's Home numbers are returned...
如果您不关心返回用户的哪些家庭号码...
回答by Remus Rusanu
Whenever you want to select only a top row from a left table for each rowin the right table you should consider using the APPLY operator instead of join, and move the join condition insidethe left join:
每当你想，只选择左表中的顶行的每一行中右表，你应该考虑使用应用运算符，而不是加入，和移动连接条件内的左连接：
SELECT u.UserID,
u.FirstName, u.LastName,
hn.PhoneNumber AS HomePhone
FROM Users u
OUTER APPLY (
SELECT TOP(1) PhoneNumber
FROM UserPhoneNumbers upn
LEFT JOIN UserPhoneNumberTypes upt
ON upn.UserPhoneNumberTypeID=upt.UserPhoneNumberTypeID
WHERE upt.PhoneNumberType='Home'
AND upn.UserID = u.UserID
ORDER BY ...) as hn
...
回答by OMG Ponies
Assuming SQL Server 2005+, use ROW_NUMBER:
假设 SQL Server 2005+，使用 ROW_NUMBER：
LEFT JOIN (SELECT UserID,
PhoneNumber AS HomePhone,
ROW_NUMBER() OVER (PARTITION BY userid ORDER BY what?) AS rank
FROM UserPhoneNumbers upn
LEFT JOIN UserPhoneNumberTypes upnt ON upnt.UserPhoneNumberTypeID = upn.UserPhoneNumberTypeID
AND upnt.PhoneNumberType='Home') AS tmpHomePhone
ON tmpHomePhone.UserID = Users.UserID
AND tmpHomePhone.rank = 1
Mind the what?placeholder for determining the first number. Omit the ORDER BY if you don't care at all...
注意what?确定第一个数字的占位符。如果您根本不在乎，请省略 ORDER BY...
回答by Marcus Adams
I assume you have some primary key field on each joined table, since UserID is not unique. I'll assume your primary key is called ID. We'll take the records with the lowest ID. This meets your "first" criteria.
我假设您在每个连接表上都有一些主键字段，因为 UserID 不是唯一的。我假设您的主键称为 ID。我们将获取具有最低 ID 的记录。这符合您的“第一”标准。
SELECT Users.UserID, Users.FirstName, Users.LastName, hp.HomePhone,
wp.WorkPhone, fn.FaxNumber
FROM Users
LEFT JOIN HomePhone hp ON hp.UserID = Users.UserID
LEFT JOIN HomePhone hp2 ON hp2.UserID = Users.UserID AND hp2.ID < hp.ID
LEFT JOIN WorkPhone wp ON wp.UserID = Users.UserID
LEFT JOIN WorkPhone wp2 ON wp2.UserID = Users.UserID AND wp2.ID < wp.ID
LEFT JOIN FaxNumber fn ON fn.UserID = Users.UserID
LEFT JOIN FaxNumber fn2 ON fn2.UserID = Users.UserID AND fn2.ID < fn.ID
WHERE hp2.ID IS NULL AND wp2.ID IS NULL AND fn2.ID IS NULL
There is a whole chapter on this type of issue, called "Ambiguous Gruops", in the book SQL Antipatterns.
在SQLAntipatterns 一书中，有一整章关于此类问题，称为“歧义组” 。
回答by Thomas
Select Users.UserID, Users.FirstName, Users.LastName
, PhoneNumbers.HomePhone
, PhoneNumbers.WorkPhone
, PhoneNumbers.FaxNumber
From Users
Left Join (
Select UPN.UserId
, Min ( Case When PN.PhoneNumberType = 'Home' Then UPN.PhoneNumber End ) As HomePhone
, Min ( Case When PN.PhoneNumberType = 'Work' Then UPN.PhoneNumber End ) As WorkPhone
, Min ( Case When PN.PhoneNumberType = 'Fax' Then UPN.PhoneNumber End ) As FaxPhone
From UserPhoneNumbers As UPN
Join (
Select Min(UPN1.UserPhoneNumberId) As MinUserPhoneNumberId
, UPNT1.PhoneNumberType
From UserPhoneNumbers As UPN1
Join UserPhoneNumberTypes As UPNT1
On UPNT1.UserPhoneNumberTypeID = UPN1.UserPhoneNumberTypeID
Where UPNT1.PhoneNumberType In('Home', 'Work', 'Fax')
Group By UPN1.UserID, UPNT.PhoneNumberType
) As PN
On PN.MinUserPhoneNumberId = UPN.UserPhoneNumberId
Group By UPN.UserId
) As PhoneNumbers
On PhoneNumbers.UserId = Users.UserId
In this solution, for each user and phone number type, I'm picking the lowest primary key value from the UserPhoneNumberstable (I guessed that the column was named UserPhoneNumberId).
在此解决方案中，对于每个用户和电话号码类型，我从UserPhoneNumbers表中选择最低的主键值（我猜该列名为UserPhoneNumberId）。
回答by Joel Coehoorn
You have to define what you mean by "first" when there are two numbers of the same type, and then add a condition to your join so that only the correct record meets the criteria. There's no other shortcut for this.
当有两个相同类型的数字时，您必须定义“第一”的含义，然后向连接添加条件，以便只有正确的记录符合条件。对此没有其他捷径。
回答by Gary
Hold on, just to understand the question.
等等，只是为了理解这个问题。
You've got two tables:
你有两个表：
Users (UserID --> x) UserPhones (UserID, PHoneType --> Phone Number) and UserID / PhoneType isn't unique.
用户 (UserID --> x) UserPhones (UserID, PHoneType --> Phone Number) 和 UserID / PhoneType 不是唯一的。
First off there's no need for temp tables:
首先，不需要临时表：
Select
x
from
Users
inner join
(
Select
top 1 y
from
FoneTypes
where
UserID = users.UseriD
and phoneType = 'typex'
) as PhoneTypex on phonetypex.UserID = users.userID
Add inner joins as necessary.
根据需要添加内部联接。
Or am I missing something?
或者我错过了什么？
回答by Frank
You could just use GROUP BY:
你可以只使用 GROUP BY：
SELECT Users.UserID,
Users.FirstName, Users.LastName,
HomePhone, WorkPhone, FaxNumber
FROM Users
LEFT JOIN
(SELECT UserID, min(PhoneNumber) AS HomePhone
FROM UserPhoneNumbers LEFT JOIN UserPhoneNumberTypes ON UserPhoneNumbers.UserPhoneNumberTypeID=UserPhoneNumberTypes.UserPhoneNumberTypeID
WHERE UserPhoneNumberTypes.PhoneNumberType='Home'
GROUP BY userID) AS tmpHomePhone
ON tmpHomePhone.UserID = Users.UserID
LEFT JOIN
(SELECT UserID, min(PhoneNumber) AS WorkPhone
FROM UserPhoneNumbers LEFT JOIN UserPhoneNumberTypes ON UserPhoneNumbers.UserPhoneNumberTypeID=UserPhoneNumberTypes.UserPhoneNumberTypeID
WHERE UserPhoneNumberTypes.PhoneNumberType='Work'
GROUP BY userID) AS tmpWorkPhone
ON tmpWorkPhone.UserID = Users.UserID
LEFT JOIN
(SELECT UserID, min(PhoneNumber) AS FaxNumber
FROM UserPhoneNumbers LEFT JOIN UserPhoneNumberTypes ON UserPhoneNumbers.UserPhoneNumberTypeID=UserPhoneNumberTypes.UserPhoneNumberTypeID
WHERE UserPhoneNumberTypes.PhoneNumberType='Fax'
GROUP BY userID) AS tmpFaxNumber
ON tmpFaxNumber.UserID = Users.UserID
Instead of min(), you could use max() as well.
除了 min()，您还可以使用 max()。
Or you could do it in one group by:
或者您可以通过以下方式在一组中完成：
SELECT Users.UserID,
Users.FirstName, Users.LastName,
max(HomePhone) as HomePhone,
max(WorkPhone) as WorkPhone,
max(FaxNumber) as FaxNumber
FROM Users
LEFT JOIN
(SELECT UserID, PhoneNumber AS HomePhone
FROM UserPhoneNumbers LEFT JOIN UserPhoneNumberTypes ON UserPhoneNumbers.UserPhoneNumberTypeID=UserPhoneNumberTypes.UserPhoneNumberTypeID
WHERE UserPhoneNumberTypes.PhoneNumberType='Home') AS tmpHomePhone
ON tmpHomePhone.UserID = Users.UserID
LEFT JOIN
(SELECT UserID, PhoneNumber AS WorkPhone
FROM UserPhoneNumbers LEFT JOIN UserPhoneNumberTypes ON UserPhoneNumbers.UserPhoneNumberTypeID=UserPhoneNumberTypes.UserPhoneNumberTypeID
WHERE UserPhoneNumberTypes.PhoneNumberType='Work') AS tmpWorkPhone
ON tmpWorkPhone.UserID = Users.UserID
LEFT JOIN
(SELECT UserID, PhoneNumber AS FaxNumber
FROM UserPhoneNumbers LEFT JOIN UserPhoneNumberTypes ON UserPhoneNumbers.UserPhoneNumberTypeID=UserPhoneNumberTypes.UserPhoneNumberTypeID
WHERE UserPhoneNumberTypes.PhoneNumberType='Fax') AS tmpFaxNumber
ON tmpFaxNumber.UserID = Users.UserID
