sql语句：如何统计1月至5月每时段的发案数量，每时段是指将24小时分割位12段

Question

也就是说，0－2，2－4，……，22－24
我的是这样写的，select count(*) from case_info where to_date(to_char('"+txtStartDate+"','yyyy-MM-dd HH24:mi'),'hh24mi') >='0000'  and  to_date(to_char('"+txtEndDate+','yyyy-MM-dd HH24:mi'),'hh24mi')<='0020' txtStartDate="2007-01-01 00:00:00"
txtEndDate="2007-05-51 00:00:00"这是统计凌晨0－2点，这样不行，请大家帮忙看看改怎么写

Answer 1

建议修改数据库表，添加日期字段casedate存放日期（格式如：2007-05-05）、所属时间段sjd（分1,2,3,..,12，可不用客户填写，系统自动根据系统时间所属时间段自动生成，或者根据系统时间默认一个，然后客户可以修改。）
sql语句
select casedate,sjd,count(*) from case_info group by casedate,sjd就出来了。

Answer 2

你那样设计数据库，填写的sql语句实现太复杂，而且有与数据库设计问题，很是影响效率。

Answer 3

如果有
txtStartDate="2007-01-01 00:00:00"
txtEndDate="2007-05-51 00:00:00"
那在sql语句中添加 where 就可以了
比如说你的表中有个字段afdate是记录案件发生时间的，sql改成
select casedate,sjd,count(*) from case_info where afdate between to_date(to_char('"+txtStartDate+"','yyyy-MM-dd HH24:mi'),'hh24mi') and to_date(to_char('"+txtEndDate+"','yyyy-MM-dd HH24:mi'),'hh24mi') group by casedate,sjd
或者
select casedate,sjd,count(*) from case_info where afdate>=to_date(to_char('"+txtEndDate+"','yyyy-MM-dd HH24:mi'),'hh24mi') and afdate<=to_date(to_char('"+txtStartDate+"','yyyy-MM-dd HH24:mi'),'hh24mi') group by casedate,sjd

Answer 4

其实你那样也能写sql语句，就是效率太差了，如果数据多速度会很慢的

Answer 5

有人提示这样写，但是报错：
select count(case when to_date(to_char('2007-04-01 00:00:00','yyyy-MM-dd HH24:mi'),'hh24mi') >='0000'                   and to_date(to_char('2007-08-31 00:00:00','yyyy-MM-dd HH24:mi'),'hh24mi')<='0200'  
                  then 1  else null end ) 
                  from case_info where type='盗窃' sql运行，报错：“ORS-01722 无效数字”

Answer 6

case when to_date(to_char('2007-04-01 00:00:00','yyyy-MM-dd HH24:mi'),'hh24mi') >='0000'                   and to_date(to_char('2007-08-31 00:00:00','yyyy-MM-dd HH24:mi'),'hh24mi')<='0200'  
                  then 1  else null end 
这里有什么用？判断日期类型的>='0000'，<='0200'
要判断也是字段呀，你搞0000什么用？

Answer 7

你那样就要很多个count（），还要动态生成sql语句。样式可能就是(如果日期字段是afdate)
select count(case when afdate>=to_date(to_char('2007-04-01 00:00:00','yyyy-MM-dd HH24:mi'),'hh24mi') and afdate<=to_date(to_char('2007-04-01 02:00:00','yyyy-MM-dd HH24:mi'),'hh24mi')) a,count(case when afdate>=to_date(to_char('2007-04-01 02:00:00','yyyy-MM-dd HH24:mi'),'hh24mi') and afdate<=to_date(to_char('2007-04-01 04:00:00','yyyy-MM-dd HH24:mi'),'hh24mi')) b...............这样根本没销率

Answer 8

给你个类似的参考吧select icpcode,'当期订购数',
sum(case when to_char(subscribe_time,'dd')='01' then subscribe_num else 0 end),
sum(case when to_char(subscribe_time,'dd')='02' then subscribe_num else 0 end),
sum(case when to_char(subscribe_time,'dd')='03' then subscribe_num else 0 end),
sum(case when to_char(subscribe_time,'dd')='04' then subscribe_num else 0 end),
sum(case when to_char(subscribe_time,'dd')='05' then subscribe_num else 0 end),
sum(case when to_char(subscribe_time,'dd')='06' then subscribe_num else 0 end),
sum(case when to_char(subscribe_time,'dd')='07' then subscribe_num else 0 end),
sum(case when to_char(subscribe_time,'dd')='08' then subscribe_num else 0 end),
sum(case when to_char(subscribe_time,'dd')='09' then subscribe_num else 0 end),
sum(case when to_char(subscribe_time,'dd')='10' then subscribe_num else 0 end),
sum(case when to_char(subscribe_time,'dd')='11' then subscribe_num else 0 end),
sum(case when to_char(subscribe_time,'dd')='12' then subscribe_num else 0 end),
sum(case when to_char(subscribe_time,'dd')='13' then subscribe_num else 0 end),
sum(case when to_char(subscribe_time,'dd')='14' then subscribe_num else 0 end),
sum(case when to_char(subscribe_time,'dd')='15' then subscribe_num else 0 end),
sum(case when to_char(subscribe_time,'dd')='16' then subscribe_num else 0 end),
sum(case when to_char(subscribe_time,'dd')='17' then subscribe_num else 0 end),
sum(case when to_char(subscribe_time,'dd')='18' then subscribe_num else 0 end),
sum(case when to_char(subscribe_time,'dd')='19' then subscribe_num else 0 end),
sum(case when to_char(subscribe_time,'dd')='20' then subscribe_num else 0 end),
sum(case when to_char(subscribe_time,'dd')='21' then subscribe_num else 0 end),
sum(case when to_char(subscribe_time,'dd')='22' then subscribe_num else 0 end),
sum(case when to_char(subscribe_time,'dd')='23' then subscribe_num else 0 end),
sum(case when to_char(subscribe_time,'dd')='24' then subscribe_num else 0 end),
sum(case when to_char(subscribe_time,'dd')='25' then subscribe_num else 0 end),
sum(case when to_char(subscribe_time,'dd')='26' then subscribe_num else 0 end),
sum(case when to_char(subscribe_time,'dd')='27' then subscribe_num else 0 end),
sum(case when to_char(subscribe_time,'dd')='28' then subscribe_num else 0 end),
sum(case when to_char(subscribe_time,'dd')='29' then subscribe_num else 0 end),
sum(case when to_char(subscribe_time,'dd')='30' then subscribe_num else 0 end),
sum(case when to_char(subscribe_time,'dd')='31' then subscribe_num else 0 end)
from temp_card_service_subscribe
where to_char(subscribe_time,'yyyymm')='200612'

Answer 9

case when 条件 then 1  else null end 这句我理解不了呢，我觉着
这样满足条件得话，无论多少，不也是统计出1条记录

Answer 10

sum(case when end)的意思是求和 当满足某条件时..当满足另一条件时 一般要伴随group by使用

Answer 11

楼主,oracle那回答的是我
你先把sql写出来,不要写在程序中,前面  wxj276(三角裤头套外面) 说的是的对的
select sum(case when 
to_date(txtStartDate,'hh24') >=to_date('00','hh24')  
and  to_date(txtEndDate,'hh24')<=to_date('02','hh24') then 1 else o end),
sum(case when 
to_date(txtStartDate,'hh24mi') >=to_date('02','hh24')  
and  to_date(txtEndDate,'hh24mi')<=to_date('04','hh24') then 1 else o end)
...//多写几个
from tablesql运行，报错：“ORS-01722 无效数字”
是转化时出错了,你仔细调下count(case when 条件 then 1  else null end) 
可以换成
sum(case when 条件 then 1  else 0 end)