如何将SQLServer中的左联接限制为第一结果?

内容来源于 Stack Overflow,并遵循CC BY-SA 3.0许可协议进行翻译与使用

  • 回答 (2)
  • 关注 (0)
  • 查看 (18)

我有一些SQL,它几乎可以完成我希望它做的事情。我正在处理三个表,一个用户,UserPhoneNumbers和UserPhoneNumberTypes。我想要一张用户名单,上面有他们的电话号码,以便出口。

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

如何修改SQL以获取列出的第一个电话号码而忽略其余号码?我在SQLServer中,我知道最重要的语句。但是如果我在左边的Join SELECT语句中添加‘TOP 1’,它只是给我数据库中的第一项,而不是每个用户的第一条目。

这是用于SQLServer 2000的。

谢谢

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
提问于
用户回答回答于

由于它是SQLServer 2000,并且排序函数已经退出,所以可以让你的子查询选择聚合:

SELECT UserID, MAX(PhoneNumber) AS HomePhone FROM [...] GROUP BY UserID
用户回答回答于

假设SQLServer 2005+,使用行_编号:

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

扫码关注云+社区