跨三个表的t-sql外连接

Question

我有三个表：

CREATE TABLE person
    (id int,
    name char(50))

CREATE TABLE eventtype
    (id int,
     description char(50))

CREATE TABLE event
    (person_id int,
     eventtype_id int,
     duration int)

我想要的是一个查询，它为我提供每个人的每种事件类型的总持续时间的列表，包括所有零条目。例如，如果有 10 个人和 15 种不同的事件类型，则无论事件表的内容如何，​​都应该返回 150 行。

我可以在两个表之间使用外连接（例如所有事件类型的持续时间），但不能使用第二个外连接。

谢谢！

Answer 1

您必须添加 对混合进行 CROSS APPLY 以获得不存在的关系。

SELECT  q.name, q.description, SUM(q.Duration)
FROM    (
          SELECT  p.Name, et.description, Duration = 0
          FROM    person p
                  CROSS APPLY eventtype et
          UNION ALL        
          SELECT  p.Name, et.description, e.duration
          FROM    person p
                  INNER JOIN event e ON e.person_id = p.id
                  INNER JOIN eventtype et ON et.id = e.eventtypeid        
        ) q
GROUP BY
        q.Name, q.description        

Answer 2

您可以交叉连接 person 和 eventtype，然后将结果连接到事件表：

SELECT
   p.Name,
   et.Description,
   COALESCE(e.duration,0)
FROM
   person p
      cross join
   eventtype et
      left join
   event e
      on
         p.id = e.person_id and
         et.id = e.eventtype_id

交叉连接是指，对于左表中的每一行，它都连接到右表中的每一行。

Answer 3

如果您希望为 person 和 eventtype 的每个组合分配一行，则建议使用 CROSS JOIN。为了获取持续时间，我们需要加入event，但这需要是OUTER加入，因为可能并不总是有一行。您对“total”的使用表明，对于给定的 person 和 event 组合，可能存在多个 event，因此我们需要那里还有一个SUM。

示例数据：

insert person values ( 1, 'Joe' )
insert person values ( 2, 'Bob' )
insert person values ( 3, 'Tim' )

insert eventtype values ( 1, 'Cake' )
insert eventtype values ( 2, 'Pie' )
insert eventtype values ( 3, 'Beer' )

insert event values ( 1, 1, 10 ) 
insert event values ( 1, 2, 10 ) 
insert event values ( 1, 2, 5 ) 
insert event values ( 2, 1, 10 ) 
insert event values ( 2, 2, 7 ) 
insert event values ( 3, 2, 8 ) 
insert event values ( 3, 3, 16 ) 
insert event values ( 1, 1, 10 ) 

查询：

SELECT
    PET.person_id
    , PET.person_name
    , PET.eventtype_id
    , PET.eventtype_description
    , ISNULL(SUM(E.duration), 0) total_duration
FROM
    (
    SELECT
        P.id person_id
        , P.name person_name
        , ET.id eventtype_id
        , ET.description eventtype_description
    FROM
        person P
        CROSS JOIN eventtype ET
    ) PET
    LEFT JOIN event E ON PET.person_id = E.person_id
                     AND PET.eventtype_id = E.eventtype_id
GROUP BY
    PET.person_id
    , PET.person_name
    , PET.eventtype_id
    , PET.eventtype_description

输出：

person_id   person_name eventtype_id eventtype_description total_duration
----------- ----------- ------------ --------------------- --------------
1           Joe         1            Cake                  20
1           Joe         2            Pie                   15
1           Joe         3            Beer                  0
2           Bob         1            Cake                  10
2           Bob         2            Pie                   7
2           Bob         3            Beer                  0
3           Tim         1            Cake                  0
3           Tim         2            Pie                   8
3           Tim         3            Beer                  16
Warning: Null value is eliminated by an aggregate or other SET operation.

(9 row(s) affected)