表1
time id
2010-10-1 1
2010-10-2 3
2010-11-10 5
2010-12-22 7
2010-12-23 9
我想要查询的结果为: 时间 个数
30 2
60 1
90 2
时间为离当前时间的天数 即 datediff(day,time,getdate()) 个数就是汇总的行数
大家有没有好点的办法?
time id
2010-10-1 1
2010-10-2 3
2010-11-10 5
2010-12-22 7
2010-12-23 9
我想要查询的结果为: 时间 个数
30 2
60 1
90 2
时间为离当前时间的天数 即 datediff(day,time,getdate()) 个数就是汇总的行数
大家有没有好点的办法?
go
create table tb
(
ddate datetime
)
insert into tb
select '2010-10-1' union all
select '2010-10-2' union all
select '2010-11-10' union all
select '2010-12-22' union all
select '2010-12-23'
SELECT DATEDIFF(DD,ddate,GETDATE()),COUNT(*) FROM TB GROUP BY DATEDIFF(DD,ddate,GETDATE())
-----------------
(无列名) (无列名)
1 1
2 1
44 1
83 1
84 1
;with t as (
select (datediff(day,time,getdate())/30+
(case when datediff(day,time,getdate())%30=0 then 0 else 1 end)
) days
from tb1
)
select days*30,count(1) cnt from t group by days
from yb
group by ceiling(datediff(day,time,getdate())*1.0/30)*30
SELECT (datediff(DD,ddate,getdate())/30+1)*30 as time ,count(*) as number FROM TB GROUP BY (datediff(DD,ddate,getdate())/30+1)*30
FROM (SELECT (DATEDIFF(day,time,GETDATE() +29) /30) AS Days
FROM tb) AS T
GROUP BY T.Days