drop table dkhokr
select distinct 日期2,会员id,day(日期2) as 日,convert(varchar(7),日期2,111) as 年月,sum(cast(订单金额 as real)) as 售出金额
into dkhokr
from kaka售出
where 日期2 between '2022-07-01' and '2022-09-30' and 会员id is not null
group by 日期2,会员id
order by 日期2
drop table dkhokr1
select a.日期2,a.日,a.年月,a.会员id,a.售出金额,sum(b.售出金额) as 当月累计售出金额
into dkhokr1
from dkhokr as a,dkhokr as b
where b.日<=a.日 and b.年月=a.年月 and b.会员id=a.会员id
group by a.日期2,a.日,a.年月,a.会员id,a.售出金额
having sum(b.售出金额)>=4000
order by a.会员id, a.日期2
drop table dkhokrkaka2
select distinct 日期2,日,年月,count(*) as kaka当月售出累计4000以上会员人数
into dkhokrkaka2
from (
select ROW_NUMBER() over(partition by 年月,会员id order by 日期2,日,年月,会员id) as 排序,* from dkhokr1
) as aa where 排序=1
group by 日期2,日,年月
order by 日期2
drop table dkhokr_kaka
select a.日期2,a.日,a.年月,a.kaka当月售出累计4000以上会员人数,sum(b.kaka当月售出累计4000以上会员人数) as kaka累计会员人数
into dkhokr_kaka
from dkhokrkaka2 as a,dkhokrkaka2 as b
where b.日<=a.日 and b.年月=a.年月
group by a.日期2,a.日,a.年月,a.kaka当月售出累计4000以上会员人数