多个自我联接和空值

debugcn 发表于 Dev

扎克·沃特金斯（Zac Watkins）

我敢肯定，这将是一个简单的解决方法，但是到此为止。

我有一个表，该表从清单中提取数据，并且我想基于一些问题的答案来构建报告。

该表的设置如下：

list_id | QuestionID | Question   | Answer
   1    |     11     | Supervisor?| Jack
   1    |     12     |  Crew 1?   | Sam
   1    |     13     |  Crew 2?   | Sally
   1    |     14     |  Crew 3?   | NULL

可能有“船员3”吗？但它通常为null，就此而言与Crew 2相同。现在，我希望报表如下所示：

Supervisor | Crew 1 | Crew 2 | Crew 3

但是，当我使用左联接自联接表时，我仅获得包括所有三个乘员组的结果，而忽略了所有具有空字段的实例。

我目前的尝试是：

SELECT
      sup.Answer AS 'Sup'
      ,C1.Answer AS '1'
      ,C2.Answer AS '2'
      ,C3.Answer AS '3'
  FROM ChecklistDetail AS sup
  LEFT JOIN ChecklistDetail AS C1 
       ON C1.listID =sup.listID

  LEFT JOIN ChecklistDetail AS C2 
       ON C2.listID =sup.listID

  LEFT JOIN ChecklistDetail AS C3 
       ON C3.listID =sup.listID

  WHERE 
  sup.QuestionID = 11
  AND C1.QuestionID = 12
  AND C2.QuestionID = 13
  AND C3.QuestionID = 14

它可能很明显，但我在Google搜索中碰到了一些砖墙，发现了一堆非常接近的答案，但不太奏效。任何帮助，将不胜感激！

MatBailie

要直接回答您的问题，请将问题id过滤器放入联接中...这样，检查将在联接过程中而不是在联接之后进行（当id可能为NULL时）。

  SELECT
      sup.Answer AS 'Sup'
      ,C1.Answer AS '1'
      ,C2.Answer AS '2'
      ,C3.Answer AS '3'
  FROM ChecklistDetail AS sup
  LEFT JOIN ChecklistDetail AS C1 
       ON C1.listID =sup.listID
       AND C1.QuestionID = 12
  LEFT JOIN ChecklistDetail AS C2 
       ON C2.listID =sup.listID
       AND C2.QuestionID = 13
  LEFT JOIN ChecklistDetail AS C3 
       ON C3.listID =sup.listID
       AND C3.QuestionID = 14
  WHERE 
      sup.QuestionID = 11

但是，更好的方法可能是条件聚合...

SELECT
  ListID,
  MAX(CASE WHEN QuestionID = 11 THEN Answer END)   AS 'sup', 
  MAX(CASE WHEN QuestionID = 12 THEN Answer END)   AS '1', 
  MAX(CASE WHEN QuestionID = 13 THEN Answer END)   AS '2', 
  MAX(CASE WHEN QuestionID = 14 THEN Answer END)   AS '3'
FROM
  ChecklistDetail
WHERE
  QuestionID IN (11,12,13,14)
GROUP BY
  ListID

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。