使用无序字段作为条件为每个员工精确选择一行

Question

我有一个如下所示的数据集。

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 字段值，因此我需要能够扩展任何解决方案以包含不仅仅是示例中显示的三个值。有什么想法吗？谢谢。

Answer 1

您需要添加一个phone_types表（Phone_Type TEXT（Whatever），Priority INTEGER）。在此表中，列出每个 Phone_Type 值一次并为其分配优先级（在您的示例中，HOME 为 1、WORK 2、OTHER 3 等）。

然后，创建一个视图，将 Phone_Types 中的 Priority 列连接到 Phone_Numbers 表（假设我们将其称为 Phone_Numbers_Ex）。

现在，对于如何从 Phone_Numbers_Ex 获取给定 emplID 的 MIN(Priority) 记录，您有多种选择，其中最清楚的可能是：

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，然后在 EmplID 和 Priority 上加入此后的 Phone_Numbers_Ex。

Answer 2

我忘了，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

Answer 3

如果允许员工针对给定电话类型拥有多个电话号码，您的要求可能不完整。我添加了一个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 优先级，phone_number_id。

最后，如果您要升级到 SQL 2005 或 SQL 2008，您可以使用 CTE 和 ROWNUMBER() OVER (ORDER BYpriority,phone_number,PARTITION BYemployee_id) <-我认为我的语法可能与括号略有不同对此，但希望它足够清楚。这样您就可以通过检查 ROWNUMBER() = 1 来获取所有员工的第一名。

Answer 4

作为在 Select 子句中使用查询的替代 gddc 答案，您可以使用左连接。您可能会获得更好的表现，但您当然应该进行测试。

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'