Как ограничить LEFT JOIN до 1-го результата в SQL Server?

У меня есть немного 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

Ответы

Ответ 1

Так как SQL Server 2000 и функции ранжирования отсутствуют, вы можете сделать агрегацию SELECT в подзапросе:

SELECT UserID, MAX(PhoneNumber) AS HomePhone FROM [...] GROUP BY UserID

Если вам все равно, КОТОРЫЙ пользователь возвращает домашние номера...

Ответ 2

Предполагая 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, если вам все равно...

Ответ 3

Всякий раз, когда вы хотите выбрать только верхнюю строку из левой таблицы для каждой строки в правой таблице, вы должны рассмотреть возможность использования оператора 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
...

Ответ 4

Я предполагаю, что у вас есть поле первичного ключа для каждой объединенной таблицы, так как 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.

Ответ 5

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).

Ответ 6

Вы должны определить, что вы подразумеваете под "первым", когда есть два числа одного типа, а затем добавить условие к вашему соединению, чтобы только правильная запись соответствовала критериям. Для этого нет другого ярлыка.

Ответ 7

Держись, просто чтобы понять вопрос.

У вас есть две таблицы:

Пользователи (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

При необходимости добавьте внутренние соединения.

Или я что-то не хватает?

Ответ 8

Вы можете просто использовать 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