试一下:
select a.会员等级,a.会员数,b.历史会员数
from (
select newlevel as 会员等级,count(1) as 会员数
from (
select id,newlevel,row_number()over(partition by id order by changedate desc) rnum from 表2where changedate<='2023-05-02' -- '2023-05-02'换成参数
) s
where rnum=1
group by newlevel
) a
left join(
select newlevel as 会员等级,count(1) as 历史会员数 from 表2
where 1=1
and 变更日期<='2023-05-02' -- '2023-05-02'换成参数
group by newlevel
) b on a.会员等级=b.会员等级