root,直接上代码
DECLARE @today DATETIME, -- 今天
@month_bg DATETIME, -- 本月初
@month_end DATETIME, -- 本月末
@num int -- 开始结束天数
SET @today= GETDATE()
SELECT @month_bg= CONVERT(VARCHAR(8),@today,120)+'01' ; -- 本月初
set @num=datediff(day,@month_bg,@today) ;
-- 查询每日日期
with t as (
select convert(varchar(10),dateadd(DAY,t2.number,t1.day),120) date
from (select @month_bg day ) t1,
(select number from MASTER..spt_values WHERE TYPE='P' AND number>=0 and number<=@num) t2 )
-- 查询每日的数据
select id=ROW_NUMBER()OVER(ORDER BY t1.date),t1.date,
(select count(*) from tablename where substring(convert(varchar,field0002,120),1,11)<=t1.date) as 总注册人数 ,
(select count(*) from tablename where substring(convert(varchar,field0002,120),1,11)=t1.date ) as 日新增人数
from t t1 where t1.date <= convert(varchar(10),@today,23)