if object_id('tb') is not null drop table tb
go
create table tb(会员号 varchar(10),日期 varchar(10))
insert into tb values('1111' , '2020-05-02')
insert into tb values('2222' , '2020-05-03')
insert into tb values('3333' , '2020-05-03')
insert into tb values('4444' , '2020-05-04')
insert into tb values('5555' , '2020-05-04')
insert into tb values('6666' , '2020-05-04')
insert into tb values('7777' , '2020-05-04')
insert into tb values('8888' , '2020-05-05')
insert into tb values('9999' , '2020-05-05')
insert into tb values('8810' , '2020-05-05')
go
SELECT * FROM TB
--计算每日会员新增数
SELECT 日期,COUNT(*) AS 会员新增数 from tb group by 日期
--计算每日会员总数变化
SELECT 日期,会员总数=(select COUNT(*) from tb where 日期<=a.日期) from tb a group by 日期
效果截图:
原创文章,转载请注明出处!