У меня есть немного SQL, который почти делает то, что я хочу, чтобы он сделал. Я работаю с тремя таблицами, Пользователи, UserPhoneNumbers и UserPhoneNumberTypes. Я пытаюсь получить список пользователей с их номерами телефона для экспорта.
Сама база данных стара и имеет некоторые проблемы целостности. Моя проблема - то, что должен только когда-либо быть 1 тип каждого номера телефона в базе данных, но это не имеет место. Когда я выполняю это, я получаю многострочные результаты для каждого человека, если они содержат, например, два "Домашних" числа.
Как я могу изменить SQL, чтобы взять первый перечисленный номер телефона и проигнорировать остающиеся числа? Я нахожусь в SQL Server, и я знаю об операторе TOP. Но если я добавляю 'TOP 1' к левому оператору выбора СОЕДИНЕНИЯ его просто предоставление мне 1-я запись в базе данных, не 1-я запись для каждого Пользователя.
Это для 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
Так как это SQL Server 2000 и функции ранжирования отсутствуют, вы можете сделать свой подзапрос SELECT агрегатным:
SELECT UserID, MAX(PhoneNumber) AS HomePhone FROM [...] GROUP BY UserID
если вам все равно, КАКИЕ из домашних номеров пользователя возвращаются ...
Предполагая 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?
для определения первого числа. Пропустите ЗАКАЗ, если вам все равно ...
Я предполагаю, что у вас есть какое-то поле первичного ключа в каждой объединенной таблице, поскольку UserID не является уникальным. Я предполагаю, что ваш первичный ключ называется 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 есть целая глава по этому типу проблем, называемая «Неоднозначные группы».
Вы можете просто использовать 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
Вместо min () вы также можете использовать max ().
Или вы можете сделать это в одной группе:
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
Когда вы хотите выбрать только верхнюю строку из левой таблицы для каждой строки в правой таблице, вам следует рассмотреть возможность использования оператора APPLY вместо join и переместить условие 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
...
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
В этом решении для каждого пользователя и типа номера телефона я выбираю наименьшее значение первичного ключа из таблицы UserPhoneNumbers
(я предположил, что столбец был назван UserPhoneNumberId
).
Вы должны определить, что вы подразумеваете под «первым», когда есть два числа одного типа, а затем добавить условие к вашему соединению, чтобы только правильная запись соответствовала критериям. Для этого нет другого ярлыка.
Постойте, просто чтобы понять вопрос.
У вас есть две таблицы:
Пользователи (UserID -> x) UserPhones (UserID, PHoneType -> Номер телефона) и UserID / PhoneType не уникален.
Во-первых, временные таблицы не нужны:
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
Добавляйте внутренние соединения по мере необходимости.
Или я что-то упустил?