sql语句 查询某一天数据每个小时的数据

Question

如何把数据库内的数据按时间统计，要求一天的24小时每个小时都要统计，然后分别输出，比如0时有多少数据，1时有多少数据这样，select count(1)
from line_movement_log a
where a.CARRIER_MVT_CNTR_INTERCHG_MD ='I'
and  a.cntr_status = 'XF'
and  a.tractor_gate_in_datetime ？？，最后这个字段是时间，我用的是oracle数据库，请高手解答，谢谢

Answer 1

ps:tractor_gate_in_datetime格式是这样的yyyy-mm-dd hh24：mi：ss

Answer 2

select to_char(a.tractor_gate_in_datetime, 'hh24'), count(1)
from line_movement_log a
where a.CARRIER_MVT_CNTR_INTERCHG_MD ='I'
and a.cntr_status = 'XF'
and  trunc(a.tractor_gate_in_datetime) = trunc(sysdate)
group by to_char(a.tractor_gate_in_datetime, 'hh24');

Answer 3

select count(1)
from line_movement_log a
where 你需要的条件
group by to_char(tractor_gate_in_datetime,'yyyy-mm-dd hh24')

Answer 4

看错了！你的只能统计当天的！漏看trunc(sysdate)了!

Answer 5

ORACLE的？？建议去ORACLE去问问

Answer 6

提供一个参考：select trunc(creation_date) as DAYS,
       SUM(DECODE(TO_CHAR(creation_date,'HH24'),'1',1,0)) AS "1",
       SUM(DECODE(TO_CHAR(creation_date,'HH24'),'2',1,0)) AS "2",
       SUM(DECODE(TO_CHAR(creation_date,'HH24'),'3',1,0)) AS "3",
       SUM(DECODE(TO_CHAR(creation_date,'HH24'),'4',1,0)) AS "4",
       SUM(DECODE(TO_CHAR(creation_date,'HH24'),'5',1,0)) AS "5",
       SUM(DECODE(TO_CHAR(creation_date,'HH24'),'6',1,0)) AS "6",
       SUM(DECODE(TO_CHAR(creation_date,'HH24'),'7',1,0)) AS "7",
       SUM(DECODE(TO_CHAR(creation_date,'HH24'),'8',1,0)) AS "8",
       SUM(DECODE(TO_CHAR(creation_date,'HH24'),'9',1,0)) AS "9",
       SUM(DECODE(TO_CHAR(creation_date,'HH24'),'10',1,0)) AS "10",
       SUM(DECODE(TO_CHAR(creation_date,'HH24'),'11',1,0)) AS "11",
       SUM(DECODE(TO_CHAR(creation_date,'HH24'),'12',1,0)) AS "12",
       SUM(DECODE(TO_CHAR(creation_date,'HH24'),'13',1,0)) AS "13",
       SUM(DECODE(TO_CHAR(creation_date,'HH24'),'14',1,0)) AS "14",
       SUM(DECODE(TO_CHAR(creation_date,'HH24'),'15',1,0)) AS "15",
       SUM(DECODE(TO_CHAR(creation_date,'HH24'),'16',1,0)) AS "16",
       SUM(DECODE(TO_CHAR(creation_date,'HH24'),'17',1,0)) AS "17",
       SUM(DECODE(TO_CHAR(creation_date,'HH24'),'18',1,0)) AS "18",
       SUM(DECODE(TO_CHAR(creation_date,'HH24'),'19',1,0)) AS "19",
       SUM(DECODE(TO_CHAR(creation_date,'HH24'),'20',1,0)) AS "20",
       SUM(DECODE(TO_CHAR(creation_date,'HH24'),'21',1,0)) AS "21",
       SUM(DECODE(TO_CHAR(creation_date,'HH24'),'22',1,0)) AS "22",
       SUM(DECODE(TO_CHAR(creation_date,'HH24'),'23',1,0)) AS "23",
       SUM(DECODE(TO_CHAR(creation_date,'HH24'),'24',1,0)) AS "24",
       --count(1) as records
       0
  from oe_order_lines_all --WHERE TRUNC(CREATION_DATE) = TO_DATE('2011/07/29','YYYY/MM/DD')    
 group by trunc(creation_date)

Answer 7

最近加group by to_char(tractor_gate_in_datetime,'yyyy-mm-dd hh24')就可以了

Answer 8

select to_char(a.tractor_gate_in_datetime, 'hh24'), count(1)
from T
where 条件 
group by to_char(a.tractor_gate_in_datetime, 'hh24');