大佬教程收集整理的这篇文章主要介绍了SQL查询-多列的SUM(WHEN x THEN 1 ELSE 0的情况),大佬教程大佬觉得挺不错的,现在分享给大家,也给大家做个参考。
我将通过以下方式更改查询:
group by
。left outer join
来确保所有数据都可用。count(<fIEldname>)
您可以消除与的比较is null
。这对于第二和第三计算值很重要。SELECT CAST(Detail.ReceiptDate AS DATE) AS "Date",
SUM(@R_458_10586@LMAILED) as @R_458_10586@lMailed,
SUM(@R_458_10586@LUNDEliNOTICESRECEIVED) as @R_458_10586@LUNDEliNOTICESRECEIVED,
SUM(TRACEUNDELNOTICESRECEIVED) as TRACEUNDELNOTICESRECEIVED
FROM ((SELEct SentDate AS "ReceiptDate", COUNT(*) AS totalR_866_11845@ailed,
NulL as @R_458_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_458_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;
以下使用做了类似的事情full outer join
:
SELECT coalesce(sd.ReceiptDate, mde.ReceiptDatE) AS "Date",
sd.@R_458_10586@lMailed, mde.@R_458_10586@LUNDEliNOTICESRECEIVED,
mde.TRACEUNDELNOTICESRECEIVED
FROM (SELEct cast(SentDate as datE) AS "ReceiptDate", COUNT(*) AS totalR_866_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_458_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;
我想看看下面是否有更好的查询方法。我想做的是创建一个摘要报告,按日期编译统计信息。
SELECT CAST(Detail.ReceiptDate AS DATE) AS 'DATE',sum(cASE WHEN Detail.Type = '@R_458_10586@lMailed' THEN 1 ELSE 0 END) AS '@R_458_10586@LMAILED',sum(cASE WHEN Detail.Type = '@R_458_10586@lReturnMail' THEN 1 ELSE 0 END) AS '@R_458_10586@LUNDELINOTICESRECEIVED',sum(cASE WHEN Detail.Type = 'TraceReturnedMail' THEN 1 ELSE 0 END) AS 'TRACEUNDELNOTICESRECEIVED'
FROM
(
SELEct SentDate AS 'ReceiptDate','@R_458_10586@lMailed' AS 'Type'
from MailDataExtract
where sentdate is not null
union all
SELEct MDE.ReturnMailDate AS 'ReceiptDate','@R_458_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
这只是查询的一个示例(在报表中使用),因为还有许多其他列,并且其他统计信息的逻辑更加复杂。是否有更优雅的方法来获取此类信息/撰写此类报告?
以上是大佬教程为你收集整理的SQL查询-多列的SUM(WHEN x THEN 1 ELSE 0的情况)全部内容,希望文章能够帮你解决SQL查询-多列的SUM(WHEN x THEN 1 ELSE 0的情况)所遇到的程序开发问题。
如果觉得大佬教程网站内容还不错,欢迎将大佬教程推荐给程序员好友。
本图文内容来源于网友网络收集整理提供,作为学习参考使用,版权属于原作者。
如您有任何意见或建议可联系处理。小编QQ:384754419,请注明来意。