我有一些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
发布于 2010-07-31 05:33:31
由于它是SQL Server2000,并且没有排名函数,因此您可以使子查询选择聚合:
SELECT UserID, MAX(PhoneNumber) AS HomePhone FROM [...] GROUP BY UserID
如果你不关心返回哪一个用户的家庭号码...
发布于 2010-07-31 05:25:27
假设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
注意用于确定第一个数字的what?
占位符。如果你一点都不关心,就省略ORDER BY。
发布于 2010-07-31 05:37:06
我假设每个连接表上都有一些主键字段,因为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
在SQL Antipatterns一书中,有一整章都是关于这类问题的,叫做“模棱两可的Gruops”。
https://stackoverflow.com/questions/3375436
复制相似问题