求一句统计产量的SQL语句

Question

一个数据表EntryNum中,有这样三个字段
Productcode    产品类型
num            数量
createdate     入库时间
productcode            num         createdate
   A                   4         2005-10-25 03:00:00      
   A                   3         2005-10-25 09:19:00
   B                   1         2005-10-25 01:10:00
   B                   3         2005-10-25 20:00:00
   A                   1         2005-10-25 21:00:00        
   C                   3         2005-10-25 10:12:00
   A                   2         2005-10-25 15:30:00
   ...                  ...        ........(早班为00:00--08:00,中班为08:00--16:00,夜班为16:00--24:00),上面列出2005-10-25日的产量怎样统计成这样的格式:产品类型         早班         中班           夜班             总计
   A              4            5              1                10
   B              1            0              3                 4
   C              0            3              0                 3
   
   ...           ...          ...            ...               ...  总计            5           8              4                 17   

Answer 1

select productcode,
sum(case when abs(mod(createdate-to_date('2005-10-25', 'yyyy-mm-dd'), 1))<0.333333333 
then num  else 0 end) "早班",
sum(case when abs(mod(createdate-to_date('2005-10-25', 'yyyy-mm-dd'), 1))>0.333333333
 and  abs(mod(createdate-to_date('2005-10-25', 'yyyy-mm-dd'), 1))<0.666666667
then num  else 0 end) "中班",
sum(case when abs(mod(createdate-to_date('2005-10-25', 'yyyy-mm-dd'), 1))>0.666666667
then num  else 0 end) "夜班"
sum(num) "总计"
from EntryNum
group by name

Answer 2

同意楼上的，zzwind5() 写得很对，佩服！

Answer 3

为什么要直接用小数来计算？是不是这样的效率会高一点？用
createdate>=to_date('2005.10.25 00','yyyy.mm.dd HH24') and 
createdate<to_date('2005.10.25 08','yyyy.mm.dd HH24') 
是不是性能比较差？？

Answer 4

SQL> select decode(grouping(productcode),1,'总计'),
  2  sum(case when createdate BETWEEN DATE '2005-10-25' AND DATE '2005-10-25' + 1/3
  3   then num  else 0 end) "早班",
  4  sum(case WHEN createdate BETWEEN DATE '2005-10-25' + 1/3 AND DATE '2005-10-25' + 2/3
  5   then num  else 0 end) "中班",
  6  sum(case when createdate BETWEEN DATE '2005-10-25' + 2/3 AND DATE '2005-10-25' + 1
  7   then num  else 0 end) "夜班",
  8  sum(num) "总计"
  9  from test2
 10  group by rollup(Productcode)
 11  /DECODE(GROUPING(PRODUCTCODE),1       早班       中班       夜班       总计
------------------------------ ---------- ---------- ---------- ----------
                                        4          5          1         10
                                        1          0          3          4
                                        0          3          0          3
总计                                    5          8          4         17

Answer 5

我修改了一下，也是别人提供的SQL语句
select productcode,
    sum(case when createdate BETWEEN DATE '2005-10-25' AND DATE '2005-10-25' + 1/3
     then num  else 0 end) "早班",
    sum(case WHEN createdate BETWEEN DATE '2005-10-25' + 1/3 AND DATE '2005-10-25' + 2/3
     then num  else 0 end) "中班",
    sum(case when createdate BETWEEN DATE '2005-10-25' + 2/3 AND DATE '2005-10-25' + 1
     then num  else 0 end) "夜班",
    sum(case when createdate BETWEEN DATE '2005-10-25'  AND DATE '2005-10-25' + 1
     then num  else 0 end) "总计"
from EntryNum
group by Productcode
UNION
select '总计',
    sum(case when createdate BETWEEN DATE '2005-10-25' AND DATE '2005-10-25' + 1/3
     then num  else 0 end) "早班",
    sum(case WHEN createdate BETWEEN DATE '2005-10-25' + 1/3 AND DATE '2005-10-25' + 2/3
     then num  else 0 end) "中班",
    sum(case when createdate BETWEEN DATE '2005-10-25' + 2/3 AND DATE '2005-10-25' + 1
     then num  else 0 end) "夜班",
    sum(case when createdate BETWEEN DATE '2005-10-25'  AND DATE '2005-10-25' + 1
     then num  else 0 end) "总计"
from EntryNum
order by productcode这样基本满足要求了，

Answer 6

是否这个查询有点太烦了，能否改进一下。
另外,领导在这个表中新添了一个销售类型，分为进出 出口两种类型能否查询设计成这样：销售类型   产品类型         早班         中班           夜班             总计
   出口      A              4            5              1                10
             C              0            3              0                 3
   内销      B              1            0              3                 4
                
            ...           ...          ...            ...               ...  总计                      5           8              4                 17

Answer 7

SQL> select decode(grouping(productcode),1,'总计',productcode),
  2    sum(case when createdate BETWEEN DATE '2005-10-25' AND DATE '2005-10-25' + 1/3
  3     then num  else 0 end) "早班",
  4    sum(case WHEN createdate BETWEEN DATE '2005-10-25' + 1/3 AND DATE '2005-10-25' + 2/3
  5     then num  else 0 end) "中班",
  6    sum(case when createdate BETWEEN DATE '2005-10-25' + 2/3 AND DATE '2005-10-25' + 1
  7     then num  else 0 end) "夜班",
  8    sum(num) "总计"
  9    from test2
 10    group by CUBE(Productcode)
 11  /DECODE(GROUPING(PRODUCTCODE),1       早班       中班       夜班       总计
------------------------------ ---------- ---------- ---------- ----------
总计                                    5          8          4         17
a                                       4          5          1         10
b                                       1          0          3          4
c                                       0          3          0          3