22,209
社区成员
发帖
与我相关
我的任务
分享
;with ach as
(
select convert(varchar(7),sj,120) date,bchao,sum(sr) as sr,0 as jy
from b
group by convert(varchar(7),sj,120),bchao
union all
select convert(varchar(7),sj,120) date,bchao,0 as sr,sum(jy) as jy
from c
group by convert(varchar(7),sj,120),bchao
)
select a.bchao,a.date,isnull(b.sr,0) sr,isnull(b.jy,0) jy
from (select distinct a.bchao,b.date from a cross join ach b) a
left join (select date,bchao,sum(sr) sr,sum(jy) jy from ach group by date,bchao) b
on a.bchao = b.bchao and a.date = b.date
select date,bchao,sr,jy
into #tb
from
(
select convert(varchar(7),sj,120) date,bchao,sum(sr) as sr,0 as jy
from b
group by convert(varchar(7),sj,120),bchao
union all
select convert(varchar(7),sj,120) date,bchao,0 as sr,sum(jy) as jy
from c
group by convert(varchar(7),sj,120),bchao
) t
select a.bchao,a.date,isnull(b.sr,0) sr,isnull(b.jy,0) jy
from (select distinct a.bchao,b.date from a cross join #tb b) a
left join (select date,bchao,sum(sr) sr,sum(jy) jy from #tb group by date,bchao) b
on a.bchao = b.bchao and a.date = b.date
drop table #tb
select distinct a.bchao,b.date from a cross join ach b
中的cross join ach b什么意思?
;with ach as
(
select convert(varchar(7),sj,120) date,bchao,sum(sr) as sr,0 as jy
from b
group by convert(varchar(7),sj,120),bchao
union all
select convert(varchar(7),sj,120) date,bchao,0 as sr,sum(jy) as jy
from c
group by convert(varchar(7),sj,120),bchao
)
--这里是将两表按年月及车牌号进行合并,其中收入占一列,加油的占另一列,方便统计
--就是上访的sum() as ..,0 as .. 等
select a.bchao,a.date,isnull(b.sr,0) sr,isnull(b.jy,0) jy
from (select distinct a.bchao,b.date from a cross join ach b) a
-- 这个a表是为了得到所有车牌号所有年月的一个笛卡尔积,就是全排列,把参与统计的年月和车牌号全排列形成一个对照表
left join (select date,bchao,sum(sr) sr,sum(jy) jy from ach group by date,bchao) b
--别名b表对年月和车牌分组,将收入和加油的费用统计后去和对照表a进行左连接得到特定年月及车牌号的信息
on a.bchao = b.bchao and a.date = b.date