select isnull(convert(char(7),a.日期,120),convert(char(7),b.日期,120))
,cast(case when sum(isnull(b.军官票,0) =0 then 0 else sum(isnull(a.普通票,0)+isnull(a.老人票,0)+isnull(a.小孩票,0)+isnull(a.学生票,0)+isnull(b.军官票,0))*100/sum(isnull(b.军官票,0)) end as varchar)+'%'
from 表一a full join 表二 b
on convert(char(7),a.日期,120)=convert(char(7),b.日期,120)
group by convert(char(7),a.日期,120),convert(char(7),b.日期,120)
,cast(case when sum(isnull(b.军官票,0) =0 then 0 else sum(isnull(a.普通票,0)+isnull(a.老人票,0)+isnull(a.小孩票,0)+isnull(a.学生票,0)+isnull(b.军官票,0))*100/sum(isnull(b.军官票,0)) end as varchar)+'%'
from 表一a full join 表二 b
on convert(char(7),a.日期,120)=convert(char(7),b.日期,120)
group by convert(char(7),a.日期,120),convert(char(7),b.日期,120)
from 表一 full join 表二 on month(日期) = month(检票日期)
group by month(日期)
when 11 then '十一'
when 12 then '十二'
else substring('一二三四五六七八九十',mm,1)
end as 月份
,cast(cast((ijpg/isumas)*100 as decimal(20,2)) as varchar)+'%' as 军官票比例
from(
select isnull(a.mm,b.mm) as mm
,isnull(a.isum,0)+isnull(b.isum,0) as isum
,cast(isnull(b.isum,0) as decimal(20,2)) as ijg
from(
select month(日期) as mm,sum(普通票+老人票+小孩票+学生票) as isum
from 表一 group by month(日期)
) a full join (
select month(检票时间) as mm,sum(军官票) as isum
from 表二 group by month(检票时间)
) b on a.mm=b.mm) aa
月份,
总人数,
军官数,
case convert(varchar(20),总票数)
when '0' then '0%'
else convert(varchar(6),round(convert(real,军官数)/convert(real,总票数),2))+'%'
end as '军官百分比'
from
(
select
case convert(char(7),a.日期,121)
when null then convert(varchar(4),b.检票时间,121)+'年'+convert(varchar(2),b.检票时间,101)+'月'
else convert(varchar(4),a.日期,121)+'年'+convert(varchar(2),a.日期,101)+'月'
end as '月份',
sum(isnull(a.普通票,0)+isnull(a.老人票,0)+isnull(a.小孩票,0)+isnull(a.学生票,0)+isnull(b.军官票,0)) as '总票数',
sum(isnull(b.军官票,0)) as '军官数'
from
表一 as a
full join
表二 b
on year(a.日期) = year(b.检票时间) and month(a.日期) = month (b.检票时间)
group by convert(char(7),a.日期,121)
) as a
order by 月份
注:检票时间--riqi
普通--- simple
老人----old
小孩----child
学生----student
军人----junren
select convert(char(7),a.riqi,120)as riqi1,sum(simple+old+child+student) as zong1 into #aaa from biao1 a group by convert(char(7),a.riqi,120)
select convert(char(7),b.riqi,120)as riqi2,sum(junren)as zong2 into #bbb from biao2 b group by convert(char(7),b.riqi,120)
select isnull(a.riqi1,b.riqi2),cast(cast(case zong2 when zong2 then zong2 else 0 end as numeric(10,2))*100/case zong1 when zong1 then zong1 else 1 end as numeric(10,2))
from #aaa a full join #bbb b on a.riqi1=b.riqi2
order by a.riqi1