我有下面的 4 个 SQL 查询,最后将它们组合成一个结果集。
我猜测,因为我在每个 SQL 部分中对相同的表进行操作,所以有一种很酷的方法可以从一个统一的 SQL 查询中获取结果。
目前我是这样做的:
select
[date] = CONVERT(DATE, M.crdate),
[tally sent to smc w/ attachment] = count(*) ,
[total MB size] = sum(cast(AD.Size as decimal (10,2)) )/1024/1024
into #tmp_Attachments_Sent_To_smc
from AttachmentDetail AD
inner join MessageAttachment MA on AD.AttachmentId = MA.AttachmentId
inner join MessageRecipient MR on MA.MessageId = MR.MessageId
inner join Message M on MR.MessageId = M.id
where AD.isinline <>1 and MR.RecipientTypeId =2 and left(mr.EmailAddress,4)='smc+'
GROUP BY CONVERT(DATE, M.crdate)
ORDER BY [date] DESC;
--select * from #tmp_Attachments_Sent_To_smc
select
[date] = CONVERT(DATE, M.crdate),
[tally sent from smc w/ attachment] = count(*) ,
[total MB size] = sum(cast(AD.Size as decimal (10,2)) )/1024/1024
into #tmp_Attachments_Sent_from_smc
from AttachmentDetail AD
inner join MessageAttachment MA on AD.AttachmentId = MA.AttachmentId
inner join MessageRecipient MR on MA.MessageId = MR.MessageId
inner join Message M on MR.MessageId = M.id
where AD.isinline <>1 and MR.RecipientTypeId =1 and left(mr.EmailAddress,4)='smc+'
GROUP BY CONVERT(DATE, M.crdate)
ORDER BY [date] DESC;
select
[date] = CONVERT(DATE, M.crdate),
[grand total sent to smc] = count(*) ,
[total MB size sent to smc] = sum(cast(Size as decimal ) )/1024/1024
into #tmp_Sent_to_smc
from Message M
where MessageSourceId=2
GROUP BY CONVERT(DATE, crdate)
ORDER BY [date] DESC;
select
[date] = CONVERT(DATE, M.crdate),
[grand total sent from smc] = count(*) ,
[total MB size sent from smc] = sum(cast(Size as decimal ) )/1024/1024
into #tmp_Sent_from_smc
from Message M
where MessageSourceId=1
GROUP BY CONVERT(DATE, crdate)
ORDER BY [date] DESC;
select A.* ,
B.[tally sent from smc w/ attachment] ,
B.[total MB size],
C.[grand total sent from smc] ,
C.[total MB size sent from smc],
D.[grand total sent to smc] ,
D.[total MB size sent to smc]
from #tmp_Attachments_Sent_To_smc A
join #tmp_Attachments_Sent_from_smc B on A.date = b.date
join #tmp_Sent_from_smc C on A.date = C.date
join #tmp_Sent_to_smc D on A.date = D.date
drop table #tmp_Attachments_Sent_To_smc
drop table #tmp_Attachments_Sent_from_smc
drop table #tmp_Sent_from_smc
drop table #tmp_Sent_to_smc