我有一些SQL几乎可以做我想让它做的事情。我正在处理三个表,一个是Users,一个是UserPhoneNumbers和UserPhoneNumberTypes。我正在尝试获取用于导出的用户及其电话号码的列表。
数据库本身很旧,并且存在一些完整性问题。我的问题是,在数据库中每个电话号码应该只有一种类型,但事实并非如此。当我运行这个命令时,我会得到每个人的多行结果,例如,如果他们包含两个"Home“数字。
如何修改SQL以获取列出的第一个电话号码,而忽略其余号码?我在SQL Server中,并且我知道TOP语句。但是如果我将'TOP 1‘添加到左连接select语句中,它只会给出数据库中的第一个条目,而不是每个用户的第一个条目。
这是针对SQL Server 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
https://stackoverflow.com/questions/3375436
复制相似问题