求一条查询语句

Question

求一条查询语句我有一个表BAL_Data，结构是这样的
B_num      B_type                 B_date                        B_machine
  1               A            2006-01-21 08:00:21                 BB
  1               A            2006-01-21 08:00:35                 BB
  1               B            2006-01-21 09:00:21                 BB
.....
.....
   1              C            2006-01-21 16:00:21                 BB
   1              D            2006-01-21 17:00:21                 BB
......
分别代表是数量、规格、日期、机器名
我想统计出这样的结果(每天的08：00－16:00是早班 16：00－24：00是中班 00：00－08：00是夜班)
B_num       B_type                         B_shift                      B_machine
  2               A                         早班                          BB
  1               B                         早班                          BB
  1               C                         中班                          BB
  1               D                         中班                          BB
......
.....
,
请问语句该怎么写？

Answer 1

如果TYPE A中有早班也有中班的怎么统计?

Answer 2

那么就写成：
B_num           B_type                    B_shift                     B_machine
  2               A                         早班                          BB
  1               A                         中班                          BB
  1               B                         早班                          BB
  1               C                         中班                          BB
  1               D                         中班                          BB
......
.....
我的意思就是根据类型班别分类汇总数据。

Answer 3

select b_num,b_type, 
decode(decode (sign(to_number(to_char(b_date,'hh24mi')) - 800),-1,'早班',
sign(to_number(to_char(b_date,'hh24mi')) - 1600)),-1,'中班','晚班') b_shift
b_machine from table;

Answer 4

b_shift 后面少写了个","不好意思。

Answer 5

SELECT 
SUM(B_num),
B_type,
CASE WHEN to_char(B_date,'HH24') >= 0 AND to_char(B_date,'HH24') < 8 THEN '夜班'
     WHEN to_char(B_date,'HH24') >= 8 AND to_char(B_date,'HH24') < 16 THEN '早班'
     WHEN to_char(B_date,'HH24') >= 16 THEN '中班'
END, 
B_machine
FROM  test_txc t
GROUP BY 
B_type,
CASE WHEN to_char(B_date,'HH24') >= 0 AND to_char(B_date,'HH24') < 8 THEN '夜班'
     WHEN to_char(B_date,'HH24') >= 8 AND to_char(B_date,'HH24') < 16 THEN '早班'
     WHEN to_char(B_date,'HH24') >= 16 THEN '中班'
END,
B_machine

Answer 6

select sum(c.B_num) as B_num,c.B_type,c.B_shift,c.B_machine
(select B_num,B_type,B_date,B_machine,(case when to_char(B_date,'hh24:mi')<'08
:00' then '夜班' when  to_char(B_date,'hh24:mi')>'16:00' then '中班' else '早班'  end ) as B_shift
from BAL_Data) c
group by c.B_type,c.B_shift,c.B_machine
having sum(c.B_num)>0这也是只考虑到B_machine是唯一的情况,不然还要再考虑

Answer 7

用case when应该可以解决吧

Answer 8

fieldsun(天涯) 正解,sbaz(万神渡劫) 误解，
各位大侠，再请问如果要对数据十分钟汇总一次（以前是按班别汇总），该怎么写呢？

Answer 9

select sum(b_num) ,b_type, 
decode(decode (sign(to_number(to_char(b_date,'hh24mi')) - 800),-1,'早班',
sign(to_number(to_char(b_date,'hh24mi')) - 1600)),-1,'中班','晚班') b_shift,
b_machine from table
group by b_type,decode(decode (sign(to_number(to_char(b_date,'hh24mi')) - 800),-1,'早班',
sign(to_number(to_char(b_date,'hh24mi')) - 1600)),-1,'中班','晚班'),b_machine;忘记做统计了。

Answer 10

按时间分解的话，把groupby语句换一下就ok了

Answer 11

按时间分解的话，把groupby语句换一下就ok了
-------------------
那么要写多少个case when ...then..when....then...when....then ..end呢？
一天可是有144个十分钟啊!!!!

Answer 12

GROUP BY (B_date - TO_DATE('20050101 010101','YYYYMMDD 24HMISS'))*24*6

Answer 13

group by trunc(to_number(To_char(Yourdate,'hh24mi'))/8,0)
看看这样行不行

Answer 14

group by trunc(to_number(To_char(Yourdate,'hh24mi'))/10,0)
看看这样行不行