我有一张注册表,大约有300K的记录.我需要一个sql语句来显示该特定日期的注册总数？
select
count('x'),CONVERT(varchar(12),date_created,111)
from reg group by
cONVERT(varchar(12),111)
order by
CONVERT(varchar(12),111)
此查询的结果：
169 2011/03/24 3016 2011/03/25 2999 2011/03/26
期望的结果：
2011/03/25 3016+169 2011/03/26 2999+3016+169
如何才能做到这一点？
解决方法
这是两个版本.我已经在一台速度非常慢的计算机上测试了超过6000天的100000行,内存不足,这表明cte版本比循环版本更快.这里建议的其他版本(到目前为止)要慢得多,前提是我已正确理解了问题.
递归CTE(10秒)
-- Table variable to hold count for each day
declare @DateCount table(d int,c int,rn int)
insert into @DateCount
select
datediff(d,date_created) as d,count(*) as c,row_number() over(order by datediff(d,date_created)) as rn
from reg
group by datediff(d,date_created)
-- Recursive cte using @DateCount to calculate the running sum
;with DateSum as
(
select
d,c,rn
from @DateCount
where rn = 1
union all
select
dc.d,ds.c+dc.c as c,dc.rn
from DateSum as ds
inner join @DateCount as dc
on ds.rn+1 = dc.rn
)
select
dateadd(d,d,0) as date_created,c as total_num
from DateSum
option (maxrecursion 0)
循环(14秒)
-- Table variable to hold count for each day
declare @DateCount table(d int,rn int,cr int)
insert into @DateCount
select
datediff(d,date_created)) as rn,0
from reg
group by datediff(d,date_created)
declare @rn int = 1
-- Update cr with running sum
update dc set
cr = dc.c
from @DateCount as dc
where rn = @rn
while @@rowcount = 1
begin
set @rn = @rn + 1
update dc set
cr = dc.c + (select cr from @DateCount where rn = @rn - 1)
from @DateCount as dc
where rn = @rn
end
-- Get the result
select
dateadd(d,cr as total_num
from @DateCount
编辑1真正快速的版本
-- Table variable to hold count for each day declare @DateCount table(d int primary key,date_created) declare @rt int = 0 declare @anchor int update @DateCount set @rt = cr = @rt + c,@anchor = d option (maxdop 1) -- Get the result select dateadd(d,cr as total_num from @DateCount order by d
