一个有关按8小时段统计的问题

显示全部楼层 · 2007-7-4 17:27:50

所有数据每天按>=0:00 =8:00 =16:00 = 0 and to_number(to_char( plansmpdate, 'hh24 ' ))7 and to_number(to_char( plansmpdate, 'hh24 ' ))15 and to_number(to_char( plansmpdate, 'hh24 ' )) = 0 and

to_number(to_char(plansmpdate, 'hh24'))7 and

to_number(to_char(plansmpdate, 'hh24'))15 and

to_number(to_char(plansmpdate, 'hh24')) =0:00 =8:00 =16:00 = 0 and

to_number(to_char(plansmpdate, 'hh24'))7 and

to_number(to_char(plansmpdate, 'hh24'))15 and

to_number(to_char(plansmpdate, 'hh24'))
执行提示不是group by 表达式

千问 · 2007-7-4 17:27:50

3楼的大大，看了你的我还是不知道怎么写呢

千问 · 2007-7-4 17:27:50

兄弟试试这个：
select plandt,banci,avg(num_result) from (
select to_char(plandate,'YYYY-MM-DD') as plandt
,case when to_number(to_char(plandate, 'hh24 '))>= 0 and to_number(to_char(plandate,'hh24 ')) = 8 and to_number(to_char(plandate,'hh24 ')) = 16 and to_number(to_char(plandate,'hh24 ')) = 0 and to_number(to_char(plandate,'hh24 ')) = 8 and to_number(to_char(plandate,'hh24 ')) = 16 and to_number(to_char(plandate,'hh24 '))
谢谢，这个有效

千问 · 2007-7-4 17:27:50

原帖由 mballack 于 2009-5-18 17:33 发表

执行提示不是group by 表达式

我没有注意，你给的语句有空格！去掉空格即可!

千问 · 2007-7-4 17:27:50

原帖由 mballack 于 2009-5-18 17:36 发表
3楼的大大，看了你的我还是不知道怎么写呢

写的就是帮你简化分班的

select to_char(plansmpdate, 'yyyy-mm-dd') as "日期",
trunc(to_char(plansmpdate, 'hh24')/8) as "班次",
avg(NUM_RESULT) "值"
from ass_smpitemresult
where ass_smpitemresult.smpItem_Name = '循环母液Ⅰ_Na2Ok g/l'
group by to_char(plansmpdate, 'yyyy-mm-dd '),

trunc(to_char(plansmpdate, 'hh24')/8)
---------------
复制代码
班次取值：
当 = 0 时，表示>=0:00 =8:00 =16:00 <24:00
如果你的需求规定是用 1， 2， 3的话，就把
trunc(to_char(plansmpdate, 'hh24')/8) 改为 trunc(to_char(plansmpdate, 'hh24')/8)+1
清楚啦
[ 本帖最后由 jackywood 于 2009-5-19 10:20 编辑 ]