麻烦有没有大神帮下忙,使用sql 按购买时间统计每天的新课人数和老客人数 感谢

[qq_41647221]

[q159846]

select rq as 日期,sum(oldry) as 老用户数,sum(newry) as 新用户数 from (
	select *,isnull((select top 1 1 from tab where b.yh=yh and rq<b.rq),0) as oldry,
	(case when not exists (select 1 from tab where b.yh=yh and rq<b.rq) then 1 else 0 end) as newry  from (
		select yh,rq from (
			select yh,convert(varchar(50),rq,23) as rq from tab
		)a group by yh,rq
	)b
)c group by rq

[qq_41647221]

引用 1 楼 q159846的回复:

select rq as 日期,sum(oldry) as 老用户数,sum(newry) as 新用户数 from (
	select *,isnull((select top 1 1 from tab where b.yh=yh and rq<b.rq),0) as oldry,
	(case when not exists (select 1 from tab where b.yh=yh and rq<b.rq) then 1 else 0 end) as newry  from (
		select yh,rq from (
			select yh,convert(varchar(50),rq,23) as rq from tab
		)a group by yh,rq
	)b
)c group by rq

你好，那个第2.4题能帮忙写一下吗？感谢哈🙏

[q159846]

--从最下层查起
select *,(select flm from tab where flm=t.一级目录) as 根目录 from (
	select t.lm as 三级目录,t.flm as 二级目录,
	(select flm from tab where flm=t.flm) as 一级目录 from tab t where lm>1001000
) t