between and 这个只能统计一个时间范围的啊。我要的是统计每个小时的啊! 你是要分组? 1点 2点 还是啥 ?你表达不清晰
between and 这个只能统计一个时间范围的啊。我要的是统计每个小时的啊! 你是要分组? 1点 2点 还是啥 ?你表达不清晰就是要分组的啊,1点,2点,3点。。24点 依次类推,统计每小时的数据select hour(CreateTime) as hours,count(*) as counts from carinfo where DATE_FORMAT(CreateTime,'%Y-%m-%d') = '2004-06-12' group by hour(CreateTime); 我这样写不行啊!
select DATEPART ( hh , [CreateTime] )as hours,count(*) as counts from [carinfo] group by DATEPART ( hh , [CreateTime] )
between and 这个只能统计一个时间范围的啊。我要的是统计每个小时的啊! 你是要分组? 1点 2点 还是啥 ?你表达不清晰就是要分组的啊,1点,2点,3点。。24点 依次类推,统计每小时的数据select hour(CreateTime) as hours,count(*) as counts from carinfo where DATE_FORMAT(CreateTime,'%Y-%m-%d') = '2004-06-12' group by hour(CreateTime); 我这样写不行啊!好像我看懂了,是不是你给某一天的时间值 例如 2014-6-17号?然后统计每个小时的数据对吧
小时的话·我记得SQL是可以截取字符串的··那能不能用截取的小时直接比较·
正好做過類似需求,MYSQL的不會,不過應該可以給你思路,以下是ORACLE的按日期>小時分組并按時間順序排序的語句: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 但是這樣是不能查到零數據的,你可以在程序中插入零值
m.DT_ACTIONDATE改為CreateTime 不小心把自己代碼COPY進去了
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
between and 这个只能统计一个时间范围的啊。我要的是统计每个小时的啊!
between and 这个只能统计一个时间范围的啊。我要的是统计每个小时的啊!
你是要分组? 1点 2点 还是啥 ?你表达不清晰
between and 这个只能统计一个时间范围的啊。我要的是统计每个小时的啊!
你是要分组? 1点 2点 还是啥 ?你表达不清晰就是要分组的啊,1点,2点,3点。。24点 依次类推,统计每小时的数据select hour(CreateTime) as hours,count(*) as counts from carinfo where DATE_FORMAT(CreateTime,'%Y-%m-%d') = '2004-06-12' group by hour(CreateTime); 我这样写不行啊!
between and 这个只能统计一个时间范围的啊。我要的是统计每个小时的啊!
你是要分组? 1点 2点 还是啥 ?你表达不清晰就是要分组的啊,1点,2点,3点。。24点 依次类推,统计每小时的数据select hour(CreateTime) as hours,count(*) as counts from carinfo where DATE_FORMAT(CreateTime,'%Y-%m-%d') = '2004-06-12' group by hour(CreateTime); 我这样写不行啊!好像我看懂了,是不是你给某一天的时间值 例如 2014-6-17号?然后统计每个小时的数据对吧
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 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