分类导航

程序问答发布时间：2022-06-02 发布网站：大佬教程 code.js-code.com

大佬教程收集整理的这篇文章主要介绍了SQL查询-多列的SUM（WHEN x THEN 1 ELSE 0的情况），大佬教程大佬觉得挺不错的，现在分享给大家，也给大家做个参考。

@H_197_0@如何解决SQL查询-多列的SUM（WHEN x THEN 1 ELSE 0的情况）？开发过程中遇到SQL查询-多列的SUM（WHEN x THEN 1 ELSE 0的情况）的问题如何解决？下面主要结合日常开发的经验，给出你关于SQL查询-多列的SUM（WHEN x THEN 1 ELSE 0的情况）的解决方法建议，希望对你解决SQL查询-多列的SUM（WHEN x THEN 1 ELSE 0的情况）有所启发或帮助； @H_772_2@我将通过以下方式更改查询：

在子查询中进行聚合。这样可以利用有关表格的更多信息来优化group by。
合并第二个和第三个子查询。它们聚集在同一列上。这需要使用left outer join来确保所有数据都可用。
通过使用，count(<fIEldname>)您可以消除与的比较is null。这对于第二和第三计算值很重要。
要组合第二个查询和第三个查询，它需要从@H_70_6@mde表中计算一个ID 。这些使用@H_70_6@mde.mdEID。

@H_772_2@以下版本通过使用来遵循您的示例union all：

SELECT CAST(Detail.ReceiptDate AS DATE) AS "Date",
       SUM(@R_256_10586@LMAILED) as @R_256_10586@lMailed,
       SUM(@R_256_10586@LUNDEliNOTICESRECEIVED) as @R_256_10586@LUNDEliNOTICESRECEIVED,
       SUM(TRACEUNDELNOTICESRECEIVED) as TRACEUNDELNOTICESRECEIVED
FROM ((SELEct SentDate AS "ReceiptDate", COUNT(*) AS totalR_446_11845@ailed,
              NulL as @R_256_10586@LUNDEliNOTICESRECEIVED, 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 @R_256_10586@LUNDEliNOTICESRECEIVED,
              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;
      )
     ) detail
GROUP BY CAST(Detail.ReceiptDate AS DATE)
ORDER BY 1;

@H_772_2@以下使用做了类似的事情full outer join：

SELECT coalesce(sd.ReceiptDate, mde.ReceiptDatE) AS "Date",
       sd.@R_256_10586@lMailed, mde.@R_256_10586@LUNDEliNOTICESRECEIVED,
       mde.TRACEUNDELNOTICESRECEIVED
FROM (SELEct cast(SentDate as datE) AS "ReceiptDate", COUNT(*) AS totalR_446_11845@ailed
      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 @R_256_10586@LUNDEliNOTICESRECEIVED,
            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.ReceiptDate
ORDER BY 1;

@H_197_0@解决方法

@H_772_2@我想看看下面是否有更好的查询方法。我想做的是创建一个摘要报告，按日期编译统计信息。

 SELECT CAST(Detail.ReceiptDate AS DATE) AS 'DATE',sum(cASE WHEN Detail.Type = '@R_256_10586@lMailed' THEN 1 ELSE 0 END) AS '@R_256_10586@LMAILED',sum(cASE WHEN Detail.Type = '@R_256_10586@lReturnMail' THEN 1 ELSE 0 END) AS '@R_256_10586@LUNDELINOTICESRECEIVED',sum(cASE WHEN Detail.Type = 'TraceReturnedMail' THEN 1 ELSE 0 END) AS 'TRACEUNDELNOTICESRECEIVED'
FROM
(
SELEct SentDate AS 'ReceiptDate','@R_256_10586@lMailed' AS 'Type'
from MailDataExtract
where sentdate is not null
union all
SELEct MDE.ReturnMailDate AS 'ReceiptDate','@R_256_10586@lReturnMail' AS 'Type'
from MailDataExtract MDE
where MDE.ReturnMailDate is not null
union all
SELEct MDE.ReturnMailDate AS 'ReceiptDate','TraceReturnedMail' AS 'Type'
from MailDataExtract MDE
    inner join DTSharedData.dbo.ScanData SD ON SD.ScanDataID = MDE.ReturnScanDataID
where MDE.ReturnMailDate is not null AND SD.ReturnMailTypEID = 1
) AS Detail
GROUP BY CAST(Detail.ReceiptDate AS DATE)
ORDER BY 1