blocks|key|1184700|text|当有两个相同类型的数字时，您必须定义“第一”是什么意思，然后向您的连接添加一个条件，以便只有正确的记录才能满足条件。没有其他捷径可以做到这一点。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1184701|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

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.

blocks|key|970318|text|假设SQL+Server+2005%2B，使用ROW_NUMBER：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|970319|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|code-block|syntax|javascript|970320|注意用于确定第一个数字的what?占位符。如果你一点都不关心，就省略ORDER+BY。|offset|length|style|CODE|970321|entityMap^0|0|0|C|5|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@$I|R|J|S|K|L]]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

Assuming SQL Server 2005+, use ROW_NUMBER:

<pre><code>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
</code></pre>

Mind the <code>what?</code> placeholder for determining the first number. Omit the ORDER BY if you don't care at all...

blocks|key|1184771|text|由于它是SQL+Server2000，并且没有排名函数，因此您可以使子查询选择聚合：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1184772|SELECT+UserID,+MAX(PhoneNumber)+AS+HomePhone+FROM+[...]+GROUP+BY+UserID|code-block|syntax|javascript|1184773|如果你不关心返回哪一个用户的家庭号码...|1184774|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Since it's SQL Server 2000 and ranking functions are out, you could make your subquery SELECTs aggregate:

<pre><code>SELECT UserID, MAX(PhoneNumber) AS HomePhone FROM [...] GROUP BY UserID
</code></pre>

iff you don't care WHICH of a user's Home numbers are returned...

blocks|key|1184934|text|我假设每个连接表上都有一些主键字段，因为UserID不是惟一的。我假设你的主键是ID。我们会取ID最小的记录。这符合你的“第一”标准。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1184935|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|code-block|syntax|javascript|1184936|在SQL+Antipatterns一书中，有一整章都是关于这类问题的，叫做“模棱两可的Gruops”。|offset|length|1184937|entityMap|0|LINK|mutability|MUTABLE|url|https://rads.stackoverflow.com/amzn/click/com/1934356557^0|0|0|1|G|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|U|8|@]|9|@$I|V|J|W|1|X]]|A|$]]|$1|K|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]]|L|$M|$5|N|O|P|A|$Q|R]]]]

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.

<pre><code>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 &lt; hp.ID
LEFT JOIN WorkPhone wp ON wp.UserID = Users.UserID
LEFT JOIN WorkPhone wp2 ON wp2.UserID = Users.UserID AND wp2.ID &lt; wp.ID
LEFT JOIN FaxNumber fn ON fn.UserID = Users.UserID
LEFT JOIN FaxNumber fn2 ON fn2.UserID = Users.UserID AND fn2.ID &lt; fn.ID
WHERE hp2.ID IS NULL AND wp2.ID IS NULL AND fn2.ID IS NULL
</code></pre>

There is a whole chapter on this type of issue, called "Ambiguous Gruops", in the book <a href="https://rads.stackoverflow.com/amzn/click/com/1934356557" rel="nofollow noreferrer" rel="nofollow noreferrer">SQL Antipatterns</a>.

blocks|key|1184988|text|您可以只使用GROUP+BY：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1184989|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|code-block|syntax|javascript|1184990|您也可以使用max()来代替min()。|1184991|或者，您可以通过以下方法在一组中完成此操作：|1184992|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|1184993|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|R|8|@]|9|@]|A|$]]|$1|K|3|L|5|D|7|S|8|@]|9|@]|A|$E|F]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

You could just use GROUP BY:

<pre><code>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
</code></pre>

Instead of min(), you could use max() as well.

Or you could do it in one group by:

<pre><code>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
</code></pre>

blocks|key|1185035|text|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|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1185036|在这个解决方案中，对于每个用户和电话号码类型，我从UserPhoneNumbers表中选择最小的主键值(我猜测该列的名称是UserPhoneNumberId)。|unstyled|offset|length|style|CODE|1185037|entityMap^0|0|P|G|1P|H|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|N|8|@$G|O|H|P|I|J]|$G|Q|H|R|I|J]]|9|@]|A|$]]|$1|K|3|-4|5|F|7|S|8|@]|9|@]|A|$]]]|L|$]]

<pre><code>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
</code></pre>

In this solution, for each user and phone number type, I'm picking the lowest primary key value from the <code>UserPhoneNumbers</code> table (I guessed that the column was named <code>UserPhoneNumberId</code>).

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. 

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.

This is for SQL Server 2000.

Thanks,

<pre><code>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
</code></pre>

How do I limit a LEFT JOIN to the 1st result in SQL Server?

SQL Server

数据库

我有一些SQL几乎可以做我想让它做的事情。我正在处理三个表，一个是Users，一个是UserPhoneNumbers和UserPhoneNumberTypes。我正在尝试获取用于导出的用户及其电话号码的列表。数据库本身很旧，并且存在一些完整性问题。我的问题是，在数据库中每个电话号码应该只有一种类型，但事实并非如此。当我...

问如何在SQL Server中将左联接限制为第一个结果？
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在SQL Server中将左联接限制为第一个结果？EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在SQL Server中将左联接限制为第一个结果？
EN