存储表: ID name logintime
1 A 2010-01-22
2 A 2010-02-22
3 A 2010-03-22
4 A 2010-04-22一般统计:select A,datepart(MM,logintime),count(ID)as T
会得到这样的结果:A 1 1
A 2 1
A 3 1
A 4 1
希望效果:
A 1 1
A 2 2
A 3 3
A 4 4
1 A 2010-01-22
2 A 2010-02-22
3 A 2010-03-22
4 A 2010-04-22一般统计:select A,datepart(MM,logintime),count(ID)as T
会得到这样的结果:A 1 1
A 2 1
A 3 1
A 4 1
希望效果:
A 1 1
A 2 2
A 3 3
A 4 4
select A,datepart(MM,logintime),count(ID)as T,row_number() order by getdate() from tb
select A,datepart(MM,logintime),count(ID)as T,row_number() over(order by getdate()) from tb
select A,datepart(MM,logintime),t=row_number() over(order by select 0) from tb
create table tb(ID int,name varchar(10),logintime datetime)
insert into tb values(1 ,'A', '2010-01-22')
insert into tb values(2 ,'A', '2010-02-22')
insert into tb values(3 ,'A', '2010-03-22')
insert into tb values(4 ,'A', '2010-04-22')
goselect name , mm, cnt = (select count(1) from
(
select name , month(logintime) mm ,logintime from tb
) n where n.name = m.name and mm <= m.mm) from
(
select name , month(logintime) mm ,logintime from tb
) mdrop table tb/*
name mm cnt
---------- ----------- -----------
A 1 1
A 2 2
A 3 3
A 4 4(所影响的行数为 4 行)
*/
select name,datepart(MM,logintime),ID as T from 表
as
(
select [name],datepart(MM,logintime) as mm
from tb
group by [name],datepart(MM,logintime)
)
select name,mm,(select COUNT(1) from t where name = a.name and mm <=a.mm)
from t as a
select name , month(logintime) ,row_number over(patition by name order by logintime) as T from tb