如何将左连接限制为SQL Server中的第一个结果?

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.

数据库本身是旧的，并且存在一些完整性问题。我的问题是，数据库中每个电话号码应该只有一种类型，但事实并非如此。当我运行这个程序时，如果每个人都包含两个“Home”数字，我就会得到多行结果。

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中，我知道上面的语句。但是如果我在左JOIN select语句中添加“TOP 1”它会给我数据库中的第一个条目，而不是每个用户的第一个条目。

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

8 个解决方案

#1

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

因为它是SQL Server 2000，排名功能已经出来，所以您可以让子查询选择聚集:

SELECT UserID, MAX(PhoneNumber) AS HomePhone FROM [...] GROUP BY UserID

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

如果你不关心哪个用户的家庭号码被退回……

#2

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...

介意什么?占位符，用于确定第一个数字。如果你一点也不在乎，就把订单省略掉。

#3

Whenever you want to select only a top row from a left table for each row in the right table you should consider using the APPLY operator instead of join, and move the join condition inside the left join:

每当您想为右表中的每一行从左表中只选择一行的顶行时，您应该考虑使用APPLY操作符而不是join，并将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
...

#4

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 UserPhoneNumbers table (I guessed that the column was named UserPhoneNumberId).

在这个解决方案中，对于每个用户和电话号码类型，我正在从UserPhoneNumbers表中选择最低的主键值(我猜测列被命名为UserPhoneNumberId)。

#5

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最低的记录。这符合您的“first”标准。

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.

在《SQL反模式》(SQL Antipatterns)一书中，有整整一章都是关于这类问题的，叫做“歧义Gruops”。

#6

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.

当有两个相同类型的数字时，必须定义“first”的含义，然后在连接中添加一个条件，以便只有正确的记录符合条件。这没有其他捷径可走。

#7

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.

您也可以使用max()代替min()。

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

#8

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) userphone (UserID、PHoneType—>电话号码)和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?

还是我漏掉了什么?