22,210
社区成员
发帖
与我相关
我的任务
分享
SELECT T1.[month],COUNT(1)C
FROM(SELECT MONTH([创建时间])AS[month]FROM[用户表]GROUP BY MONTH([创建时间]))AS T1
JOIN [用户表] T2 ON MONTH(T2.[创建时间])<=T1.[month]
GROUP BY T1.[month]
不考虑跨年,可以这样查询
已修改,应该兼容MYSQL
;WITH CTE AS(
SELECT[month],COUNT(*)C FROM TB GROUP BY[month]
)
SELECT T1.[month],SUM(T2.C)C
FROM CTE T1
JOIN CTE T2 ON T1.[month]>=T2.[month]
GROUP BY T1.[month]
Declare @TmpData Table(
M Int,
P Int)
Insert Into @TmpData
Select 1,200
Union
Select 2,300
Union
Select 3,400
Select M,(Select SUM(P) From @TmpData Where M<= A.M) From @TmpData A
DECLARE @T TABLE(ID INT,Quantity INT)
INSERT INTO @T VALUES
(1,200),
(2,500),
(3,200),
(4,700)
SELECT ID,CAT.accQuantity
FROM @T T
CROSS APPLY
(
SELECT SUM(Quantity) accQuantity FROM @T WHERE ID <= T.ID
) AS CAT
-- drop table [用户表]
create table [用户表]([创建时间] datetime)
insert into [用户表]
select '2015-01-01' union all
select '2015-01-01' union all
select '2015-02-01' union all
select '2015-02-01' union all
select '2015-03-01' union all
select '2015-03-01' union all
select '2015-03-01'
;with [用户a] as(
select row_number() over(order by year([创建时间]),month([创建时间])) id
,year([创建时间]) 年,month([创建时间]) 月,count(*) as 记录
from [用户表]
group by year([创建时间]),month([创建时间])
)
, [用户b] as(
select id,年,月,记录 as 累计 from [用户a] where id = 1
union all
select a.id,a.年,a.月,a.记录+b.累计
from [用户a] a inner join [用户b] b on a.id=b.id+1
)
select * from [用户b] order by 年,月,id