SQL查询-多列的SUM（WHEN x THEN 1 ELSE 0的情况）

我将通过以下方式更改查询：

1. 在子查询中进行聚合。这样可以利用有关表格的更多信息来优化

   group by

   。
2. 合并第二个和第三个子查询。它们聚集在同一列上。这需要使用

   left outer join

   来确保所有数据都可用。
3. 通过使用，

   count(<fieldname>)

   您可以消除与的比较

   is null

   。这对于第二和第三计算值很重要。
4. 要组合第二个查询和第三个查询，它需要从

   mde

   表中计算一个ID 。这些使用

   mde.mdeid

   。

以下版本通过使用来遵循您的示例

union all

SELECt CAST(Detail.ReceiptDate AS DATE) AS "Date",       SUM(TOTALMAILED) as TotalMailed,       SUM(TOTALUNDELINOTICESRECEIVED) as TOTALUNDELINOTICESRECEIVED,       SUM(TRACEUNDELNOTICESRECEIVED) as TRACEUNDELNOTICESRECEIVEDFROM ((select SentDate AS "ReceiptDate", COUNT(*) as TotalMailed,   NULL as TOTALUNDELINOTICESRECEIVED, NULL as TRACEUNDELNOTICESRECEIVED       from MailDataExtract       where SentDate is not null       group by SentDate      ) union all      (select MDE.ReturnMailDate AS ReceiptDate, 0,   COUNT(distinct mde.mdeid) as TOTALUNDELINOTICESRECEIVED,   SUM(case when sd.ReturnMailTypeId = 1 then 1 else 0 end) as TRACEUNDELNOTICESRECEIVED       from MailDataExtract MDE left outer join DTSharedData.dbo.ScanData SD ON SD.ScanDataID = MDE.ReturnScanDataID       group by MDE.ReturnMailDate;      )     ) detailGROUP BY CAST(Detail.ReceiptDate AS DATE)ORDER BY 1;

以下使用做了类似的事情

full outer join

SELECt coalesce(sd.ReceiptDate, mde.ReceiptDate) AS "Date",       sd.TotalMailed, mde.TOTALUNDELINOTICESRECEIVED,       mde.TRACEUNDELNOTICESRECEIVEDFROM (select cast(SentDate as date) AS "ReceiptDate", COUNT(*) as TotalMailed      from MailDataExtract      where SentDate is not null      group by cast(SentDate as date)     ) sd full outer join    (select cast(MDE.ReturnMailDate as date) AS ReceiptDate, COUNT(distinct mde.mdeID) as TOTALUNDELINOTICESRECEIVED, SUM(case when sd.ReturnMailTypeId = 1 then 1 else 0 end) as TRACEUNDELNOTICESRECEIVED     from MailDataExtract MDE left outer join          DTSharedData.dbo.ScanData SD          ON SD.ScanDataID = MDE.ReturnScanDataID     group by cast(MDE.ReturnMailDate as date)    ) mde    on sd.ReceiptDate = mde.ReceiptDateORDER BY 1;