drop table appcjrs
select a1.日期2,day(a1.日期2) as 日,convert(varchar(7),a1.日期2,111) as 年月,a1.kacnapp售出人数,a1.kacnapp累计售出人数,a2.kltapp售出人数,a2.kltapp累计售出人数,
isnull(a1.kacnapp售出人数,0)+isnull(a2.kltapp售出人数,0) as 两站售出人数,
isnull(a1.kacnapp累计售出人数,0)+isnull(a2.kltapp累计售出人数,0) as 两站累计售出人数
into appcjrs
from (
select *,sum(kacnapp售出人数) over(partition by convert(varchar(7),日期2,111) order by 日期2) as kacnapp累计售出人数 from (
select distinct 日期2,count(*) as kacnapp售出人数 from (
select distinct 日期2,会员id from kacn售出
where 日期2 between '2022-07-01' and '2022-09-30'
and 设备1 in ('ios','android')
and 会员id not in
(
select distinct 会员id
from kacn售出 as b1
where convert(varchar(7),b1.日期2,111)=convert(varchar(7),kacn售出.日期2,111)
and day(kacn售出.日期2)>day(b1.日期2)
and b1.日期2 between '2022-07-01' and '2022-09-30'
and b1.设备1 in ('ios','android')
)
) as a1
group by 日期2
) as a2
--order by 日期2
) as a1
left join
(
select *,sum(kltapp售出人数) over(partition by convert(varchar(7),日期2,111) order by 日期2) as kltapp累计售出人数 from (
select distinct 日期2,count(*) as kltapp售出人数 from (
select distinct 日期2,会员id from klt售出
where 日期2 between '2022-07-01' and '2022-09-30'
and 设备1 in ('ios','android')
and 会员id not in
(
select distinct 会员id
from klt售出 as b1
where convert(varchar(7),b1.日期2,111)=convert(varchar(7),klt售出.日期2,111)
and day(klt售出.日期2)>day(b1.日期2)
and b1.日期2 between '2022-07-01' and '2022-09-30'
and b1.设备1 in ('ios','android')
)
) as a1
group by 日期2
) as a2
--order by 日期2
) as a2
on a1.日期2=a2.日期2
order by a1.日期2
select *,sum(kacnapp售出人数) over(partition by convert(varchar(7),日期2,111) order by 日期2) as kacnapp累计售出人数 from (
select distinct 日期2,count(*) as kacnapp售出人数 from (
select distinct 日期2,会员id from kacn售出
where 日期2 between '2022-07-01' and '2022-09-30'
and 设备1 in ('ios','android')
and 会员id not in
(
select distinct 会员id
from kacn售出 as b1
where convert(varchar(7),b1.日期2,111)=convert(varchar(7),kacn售出.日期2,111)
and day(kacn售出.日期2)>day(b1.日期2)
and b1.日期2 between '2022-07-01' and '2022-09-30'
and b1.设备1 in ('ios','android')
)
) as a1
group by 日期2
) as a2