62,046
社区成员
发帖
与我相关
我的任务
分享
select createdate,
COUNT(case when datename(hh,createdate)=1 then 1 else 0 end) as '1',
COUNT(case when datename(hh,createdate)=2 then 1 else 0 end) as '2',
COUNT(case when datename(hh,createdate)=3 then 1 else 0 end) as '3',
COUNT(case when datename(hh,createdate)=4 then 1 else 0 end) as '4',
COUNT(case when datename(hh,createdate)=5 then 1 else 0 end) as '5',
COUNT(case when datename(hh,createdate)=6 then 1 else 0 end) as '6',
COUNT(case when datename(hh,createdate)=7 then 1 else 0 end) as '7',
COUNT(case when datename(hh,createdate)=8 then 1 else 0 end) as '8',
COUNT(case when datename(hh,createdate)=9 then 1 else 0 end) as '9',
COUNT(case when datename(hh,createdate)=10 then 1 else 0 end) as '10',
COUNT(case when datename(hh,createdate)=11 then 1 else 0 end) as '11',
COUNT(case when datename(hh,createdate)=12 then 1 else 0 end) as '12',
COUNT(case when datename(hh,createdate)=13 then 1 else 0 end) as '13',
COUNT(case when datename(hh,createdate)=14 then 1 else 0 end) as '14',
COUNT(case when datename(hh,createdate)=15 then 1 else 0 end) as '15',
COUNT(case when datename(hh,createdate)=16 then 1 else 0 end) as '16',
COUNT(case when datename(hh,createdate)=17 then 1 else 0 end) as '17',
COUNT(case when datename(hh,createdate)=18 then 1 else 0 end) as '18',
COUNT(case when datename(hh,createdate)=19 then 1 else 0 end) as '19',
COUNT(case when datename(hh,createdate)=20 then 1 else 0 end) as '20',
COUNT(case when datename(hh,createdate)=21 then 1 else 0 end) as '21',
COUNT(case when datename(hh,createdate)=22 then 1 else 0 end) as '22',
COUNT(case when datename(hh,createdate)=23 then 1 else 0 end) as '23',
COUNT(case when datename(hh,createdate)=24 then 1 else 0 end) as '24'
from tb
group by createdate
select to_char(CreateTime,'yyyy-MM-dd') as DATE,--日期分組
to_char(CreateTime,'HH24') as HOUR,--小時分組
count(*) as TOTAL --統計條數
from carinfo Where (to_char(m.DT_ACTIONDATE,'yyyy-mm-dd') = ‘2014-06-19’
group by to_char(CreateTime,'yyyy-MM-dd'),to_char(CreateTime,'HH24')
order by to_char(CreateTime,'yyyy-MM-dd'),to_char(CreateTime,'HH24') asc
但是這樣是不能查到零數據的,你可以在程序中插入零值select hour(CreateTime) as hours,count(*) as counts from carinfo where DATE_FORMAT(CreateTime,'%Y-%m-%d') = '2004-06-12' group by hour(CreateTime);
我这样写不行啊![/quote]
好像我看懂了,是不是你给某一天的时间值 例如 2014-6-17号?然后统计每个小时的数据对吧select DATEPART ( hh , [CreateTime] )as hours,count(*) as counts from [carinfo] group by DATEPART ( hh , [CreateTime] )
select hour(CreateTime) as hours,count(*) as counts from carinfo where DATE_FORMAT(CreateTime,'%Y-%m-%d') = '2004-06-12' group by hour(CreateTime);
我这样写不行啊!