Вопрос:
У меня есть немного SQL, который почти делает то, что я хочу, чтобы он делал. Я работаю с тремя таблицами, пользователями, UserPhoneNumbers и UserPhoneNumberTypes. Я пытаюсь получить список пользователей с их номерами телефонов для экспорта.
Сама база данных устарела и имеет некоторые проблемы с целостностью. Моя проблема заключается в том, что в базе данных должно быть только 1 тип каждого номера телефона, но это не так. Когда я запускаю это, я получаю многострочные результаты для каждого человека, если они содержат, например, два “домашних” номера.
Как я могу изменить SQL, чтобы перенести первый номер телефона и проигнорировать оставшиеся номера? Я нахожусь в SQL Server, и я знаю о заявлении TOP. Но если я добавлю “TOP 1” в оператор выбора LEFT JOIN, он просто предоставит мне 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
Если вам все равно, КОТОРЫЙ пользователь возвращает домашние номера…
Ответ №1
Предполагая 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? placeholder для определения первого числа. Опустите ORDER BY, если вам все равно…
Ответ №2
Всякий раз, когда вы хотите выбрать только верхнюю строку из левой таблицы для каждой строки в правой таблице, вы должны рассмотреть возможность использования оператора APPLY вместо соединения и переместить условие соединения в левом соединении:
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 … Ответ №3
Я предполагаю, что у вас есть поле первичного ключа для каждой объединенной таблицы, так как 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
Существует целая глава по этому типу проблем, называемая “Неоднозначные Gruops”, в книге SQL Antipatterns.
Ответ №4Select 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).
Ответ №5
Вы должны определить, что вы подразумеваете под “первым”, когда есть два числа одного типа, а затем добавить условие к вашему соединению, чтобы только правильная запись соответствовала критериям. Для этого нет другого ярлыка.
Ответ №6
Держись, просто чтобы понять вопрос.
У вас есть две таблицы:
Пользователи (UserID → x) UserPhones (UserID, PHoneType → Номер телефона) и UserID/PhoneType не уникален.
Во-первых, нет необходимости в таблицах temp:
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
При необходимости добавьте внутренние соединения.
Или я что-то не хватает?
Ответ №7
Вы можете просто использовать 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
