嵌套 SQL 语句

Question

我有一个人员表，还有一个联系人类型表（电子邮件、电话等） 和一个用于保存人员联系人的联结表。

我想构建一个查询来获取一条记录中每个人的电子邮件、电话、邮政地址，

因此我将有 4 列结果集用户名、电话、电子邮件、邮政地址

当我使用 SQL Server 2000 时，XML 不是一个选项。

任何帮助使其发挥作用的帮助将不胜感激。

Answer 1

您未能提供有关列的任何有用的详细信息，因此我不得不猜测，但我认为您正在寻找类似的内容。

WITH Persons(PersonId, UserName) AS
(
SELECT 1, 'Bob' UNION ALL
SELECT 2, 'Bill'
), ContactTypes(ContactTypeId, Name) AS
(
SELECT 1, 'Tel' UNION ALL
SELECT 2, 'Email' UNION ALL
SELECT 3, 'PO'
),PersonContacts(PersonId, ContactTypeId, Value) As
(
SELECT 1,1,CAST('(01223) 123456' AS VARCHAR(50)) UNION ALL
SELECT 1,2,CAST('[email protected]' AS VARCHAR(50)) UNION ALL
SELECT 1,3,CAST('1 Acacia Avenue' AS VARCHAR(50)) UNION ALL
SELECT 2,1,CAST('(01223) 654321' AS VARCHAR(50)) UNION ALL
SELECT 2,2,CAST('[email protected]' AS VARCHAR(50)) 
)

/*The above Common Table Expressions are just for demo purposes and so you 
can see the assumptions. They will not work on SQL Server 2000. You just need 
the below.*/
SELECT P.UserName, 
       /*I've just used the Ids rather than bothering to join on ContactTypes*/
       MAX(CASE WHEN C.ContactTypeId = 1 then C.Value END) Tel,
       MAX(CASE WHEN C.ContactTypeId = 2 then C.Value END) Email,
       MAX(CASE WHEN C.ContactTypeId = 3 then C.Value END) PO
FROM Persons P
LEFT JOIN PersonContacts C ON C.PersonId = P.PersonId
GROUP BY P.PersonId,P.UserName

退货

UserName Tel                 Email               PO
-------- ------------------- ------------------- ------------------
Bob      (01223) 123456      [email protected]     1 Acacia Avenue
Bill     (01223) 654321      [email protected]    NULL

Answer 2

我认为你想要这样：

SELECT p.username, email.value as email, tel.value as tel, po.value as po
FROM persons p
LEFT JOIN contacts as email ON contacts.person_id = person.id
LEFT JOIN contacts as tel ON contacts.person_id = person.id
LEFT JOIN contacts as po ON contacts.person_id = person.id
WHERE email.type = 'email'
AND tel.type = 'tel'
AND po.type = 'po'

所以你基本上加入联系人表 3 次，因为联系人表包含 3 种不同形式的联系人。这是一种多态方法，它不能很好地映射到 SQL

这个查询的结果应该是这样的：

username | email           | tel       | po
John     | [email protected]   | 012364342 | 1234AA
Sarah    | [email protected] | NULL      | NULL

我认为马丁的解决方案也可以工作，但我对 CASE-WHEN-THEN-END 不太满意

Answer 3

我们必须猜测您的架构，因为您没有在问题的初始版本中向我们提供关键信息：

假设的架构

• 表：人员 - 用户名 (PK)，...
• 表：联系人 - ContactType ('Tel', 'Email', 'PO'), ContactInfo, ContactID (PK), ...
• 表：连接点 - 用户名，ContactID - 组合上的 PK，每列上的 FK

可能的查询

SELECT P.UserName, T.Tel, E.Email, O.PO
  FROM Persons AS P
  LEFT JOIN (SELECT J.UserName, C.ContactInfo AS Tel
               FROM Contacts AS C
               JOIN Junction AS J ON C.ContactId = J.ContactID
              WHERE C.ContactType = 'Tel') AS T
       ON P.UserName = T.UserName
  LEFT JOIN (SELECT J.UserName, C.ContactInfo AS Email
               FROM Contacts AS C
               JOIN Junction AS J ON C.ContactId = J.ContactID
              WHERE C.ContactType = 'Email') AS E
       ON P.UserName = E.UserName
  LEFT JOIN (SELECT J.UserName, C.ContactInfo AS PO
               FROM Contacts AS C
               JOIN Junction AS J ON C.ContactId = J.ContactID
              WHERE C.ContactType = 'PO') AS O
       ON P.UserName = O.UserName

请注意，应强制执行一些复杂的约束使查询正常工作。第一个是给定人员有零个或一个电子邮件地址、零个或一个电话号码以及零个或一个 PO（这是邮局地址吗？）。如果某人拥有多个这些条目，您根本不会告诉我们什么可能是合适的输出。而且约束执行起来很复杂，因为最简单的版本需要对用户名和联系人类型的组合进行唯一约束，但这些列不会一起存储在任何单个表中。