У меня есть набор данных, который похож на следующее.
EMPLID PHONE_TYPE PHONE
------ ---------- --------
100 HOME 111-1111
100 WORK 222-2222
101 HOME 333-3333
102 WORK 444-4444
103 OTHER 555-5555
Я хочу выбрать точно одну строку для каждого сотрудника, использующего поле PHONE_TYPE для установления предпочтений. Я хочу Домашний телефон, если у сотрудника есть тот, как имеет место для сотрудника 100 и 101. Если Домашний телефон не присутствует, я хочу число РАБОТЫ (сотрудник 102), и как последнее прибежище я возьму ДРУГОЕ число в качестве с сотрудником 103. В действительности моя таблица имеет приблизительно дюжину значений для поля PHONE_TYPE, таким образом, я должен смочь расширить любое решение включать больше, чем просто три значения, я показал в примере. Какие-либо мысли?Спасибо.
Я забыл, поддерживает ли Server 2000 Coalesce? Если да, думаю, это сработает:
Select Distinct EmplID, Coalesce(
(Select Phone from Employees where emplid = e1.emplid and phone_type = 'HOME'),
(Select Phone from Employees where emplid = e1.emplid and phone_type = 'WORK'),
(Select Phone from Employees where emplid = e1.emplid and phone_type = 'OTHER')
) as Phone
From Employees e1
В качестве альтернативы ответу g.d.d.c, использующему запросы в предложении Select, можно использовать левые соединения. Возможно, вы получите более высокую производительность, но, конечно, вам следует протестировать.
SELECT
e1.iD,
Coalesce(phoneHome.Phone,phoneWork.Phone,phoneOther) phone
FROm
employees e1
LEFT JOIN phone phoneHome
ON e1.emplId = phoneHome
and phone_type = 'HOME'
LEFT JOIN phone phoneWork
ON e1.emplId = phoneWork
and phone_type = 'WORK'
LEFT JOIN phone phoneWork
ON e1.emplId = phoneOTHER
and phone_type = 'OTHER'
Вам нужно добавить таблицу phone_types (Phone_Type TEXT (без разницы), Priority INTEGER).В этой таблице перечислите каждое значение Phone_Type один раз и назначьте ему приоритет (в вашем примере HOME будет 1, WORK 2, OTHER 3 и так далее).
Затем создайте представление, которое объединяет столбец Priority из Phone_Types с таблицей Phone_Numbers (представьте, что мы называем его Phone_Numbers_Ex).
Теперь у вас есть несколько вариантов того, как получить запись из Phone_Numbers_Ex с MIN (Priority) для данного emplID, из которых, вероятно, наиболее ясным является:
SELECT * FROM Phone_Numbers_Ex P1 WHERE NOT EXISTS
(SELECT * FROM Phone_Numbers_Ex P2 WHERE P2.EmplID = P1.EmplID AND P2.Priority < P1.Priority)
Другой способ - объявить другое представление или внутренний запрос вместе с строки SELECT EmplID, MIN (Priority) AS Priority FROM Phone_Numbers_Ex GROUP BY EmplID
, а затем присоединение к этому обратно Phone_Numbers_Ex как для EmplID, так и для Priority.
Ваши требования могут быть неполными, если сотруднику разрешено иметь более одного телефонного номера для данного типа телефона. Я добавил phone_number_id просто для уникальности и предположил, что вам нужен наименьший id, если у человека есть два телефона одного типа. Это довольно произвольно, но вы можете заменить это своей собственной бизнес-логикой.
Я также предположил наличие таблицы Phone_Types, которая включает в себя приоритет того, какой номер телефона должен быть использован. Если у вас еще нет этой таблицы, вам, вероятно, следует добавить ее. Если ничего другого нет, она позволит вам ограничить типы телефонов с помощью внешнего ключа.
SELECT
PN1.employee_id,
PN1.phone_type,
PN1.phone_number
FROM
Phone_Numbers PN1
INNER JOIN Phone_Types PT1 ON
PT1.phone_type = PN1.phone_type
WHERE
NOT EXISTS
(
SELECT *
FROM
Phone_Numbers PN2
INNER JOIN Phone_Types PT2 ON
PT2.phone_type = PN2.phone_type AND
(
(PT2.priority < PT1.priority)
--OR (PT2.priority = PT1.priority AND PN2.phone_number_id > PN1.phone_number_id)
)
)
Вы также можете реализовать это с помощью LEFT JOIN вместо NOT EXISTS или использовать TOP, если вы ищете номер телефона для одного сотрудника. Просто сделайте TOP 1 ORDER BY priority, phone_number_id.
Наконец, если вы перейдете на SQL 2005 или SQL 2008, вы можете использовать CTE с ROWNUMBER() OVER (ORDER BY priority, phone_number, PARTITION BY employee_id) <- Я думаю, что мой синтаксис может быть немного неправильным с круглыми скобками, но надеюсь, что он достаточно понятен. Это позволит вам получить высший приоритет для всех сотрудников, проверив, что ROWNUMBER() = 1.