求一SQL语句或存储过程

Question

例如设备有 A1,A2,A3,A4  其中A4没加油现有一表数据如下
设备  加油量    加油日期
A1     10       2009-10-1A2     2        2009-10-2A3     3        2009-10-1现在想得到10月份每天每台设备的加油汇总表，如下日期
设备   1    2    3   4   5 。 31  1-31A1    10                                  10  A2          2                             2A3    3                                   3A4                                        0日统计 13   2                             15请高手帮忙，分不够再加

Answer 1

select sum(加油量)  
from YourTable
where 加油日期 like '2009-10%'
group by 设备,加油日期where 加油日期 like '2009-10%'
这个还可以改为限定加油日期在10-01到11-01之间

Answer 2

create table a(machine varchar2(20), oil number, op_date date);
insert into a values('A1',10,to_date('2009/10/01','yyyy/mm/dd'));
insert into a values('A2',2,to_date('2009/10/02','yyyy/mm/dd'));
insert into a values('A3',3,to_date('2009/10/01','yyyy/mm/dd'));
select machine, 
       case when op_date = LAST_DAY(add_months(to_date('2009/10','yyyy/mm'),-1)) + 1 then oil end "1",
       case when op_date = LAST_DAY(add_months(to_date('2009/10','yyyy/mm'),-1)) + 2 then oil end "2",
       case when op_date = LAST_DAY(add_months(to_date('2009/10','yyyy/mm'),-1)) + 3 then oil end "3", 
       case when op_date = LAST_DAY(add_months(to_date('2009/10','yyyy/mm'),-1)) + 4 then oil end "4",
       sum(oil) over(partition by a.machine) "1-31"
from   a
where  op_date between to_date('2009/10/01','yyyy/mm/dd') and to_date('2009/10/31','yyyy/mm/dd')
union
select 'daily sum' machine,
       (select sum(oil) "1" from a where op_date = LAST_DAY(add_months(to_date('2009/10','yyyy/mm'),-1)) + 1),
       (select sum(oil) "2" from a where op_date = LAST_DAY(add_months(to_date('2009/10','yyyy/mm'),-1)) + 2),
       (select sum(oil) "3" from a where op_date = LAST_DAY(add_months(to_date('2009/10','yyyy/mm'),-1)) + 3),
       (select sum(oil) "4" from a where op_date = LAST_DAY(add_months(to_date('2009/10','yyyy/mm'),-1)) + 4),
       (select sum(oil) "1-31" from a where op_date between to_date('2009/10/01','yyyy/mm/dd') 
                                                   and to_date('2009/10/31','yyyy/mm/dd') )
from   a;

Answer 3

这个用procedure做应该好点或者试试rollup,cube

Answer 4

SELECT A.设备,SUM(A.DAY1) AS DAY1
,SUM(A.DAY2) AS DAY2
,SUM(A.DAY3) AS DAY3
,SUM(A.DAY4) AS DAY4
,SUM(A.DAY5) AS DAY5
,SUM(A.DAY6) AS DAY6
,SUM(A.DAY7) AS DAY7
,SUM(A.DAY8) AS DAY8
,SUM(A.DAY9) AS DAY9
,SUM(A.DAY10) AS DAY10
,SUM(A.DAY11) AS DAY11
,SUM(A.DAY12) AS DAY12
,SUM(A.DAY13) AS DAY13
,SUM(A.DAY14) AS DAY14
,SUM(A.DAY15) AS DAY15
,SUM(A.DAY16) AS DAY16
,SUM(A.DAY17) AS DAY17
,SUM(A.DAY18) AS DAY18
,SUM(A.DAY19) AS DAY19
,SUM(A.DAY20) AS DAY20
,SUM(A.DAY21) AS DAY21
,SUM(A.DAY22) AS DAY22
,SUM(A.DAY23) AS DAY23
,SUM(A.DAY24) AS DAY24
,SUM(A.DAY25) AS DAY25
,SUM(A.DAY26) AS DAY26
,SUM(A.DAY27) AS DAY27
,SUM(A.DAY28) AS DAY28
,SUM(A.DAY29) AS DAY29
,SUM(A.DAY30) AS DAY30
,SUM(A.DAY31) AS DAY31
FROM(
select 设备,DECODE(to_char(加油日期 ,'DD'),'01', 加油量,0)  AS DAY1
,DECODE(to_char(加油日期 ,'DD'),'02', 加油量,0)  AS DAY2
,DECODE(to_char(加油日期 ,'DD'),'03', 加油量,0)  AS DAY3
,DECODE(to_char(加油日期 ,'DD'),'04', 加油量,0)  AS DAY4
,DECODE(to_char(加油日期 ,'DD'),'05', 加油量,0)  AS DAY5
,DECODE(to_char(加油日期 ,'DD'),'06', 加油量,0)  AS DAY6
,DECODE(to_char(加油日期 ,'DD'),'07', 加油量,0)  AS DAY7
,DECODE(to_char(加油日期 ,'DD'),'08', 加油量,0)  AS DAY8
,DECODE(to_char(加油日期 ,'DD'),'09', 加油量,0)  AS DAY9
,DECODE(to_char(加油日期 ,'DD'),'10', 加油量,0)  AS DAY10
,DECODE(to_char(加油日期 ,'DD'),'11', 加油量,0)  AS DAY11
,DECODE(to_char(加油日期 ,'DD'),'12', 加油量,0)  AS DAY12
,DECODE(to_char(加油日期 ,'DD'),'13', 加油量,0)  AS DAY13
,DECODE(to_char(加油日期 ,'DD'),'14', 加油量,0)  AS DAY14
,DECODE(to_char(加油日期 ,'DD'),'15', 加油量,0)  AS DAY15
,DECODE(to_char(加油日期 ,'DD'),'16', 加油量,0)  AS DAY16
,DECODE(to_char(加油日期 ,'DD'),'17', 加油量,0)  AS DAY17
,DECODE(to_char(加油日期 ,'DD'),'18', 加油量,0)  AS DAY18
,DECODE(to_char(加油日期 ,'DD'),'19', 加油量,0)  AS DAY19
,DECODE(to_char(加油日期 ,'DD'),'20', 加油量,0)  AS DAY20
,DECODE(to_char(加油日期 ,'DD'),'21', 加油量,0)  AS DAY21
,DECODE(to_char(加油日期 ,'DD'),'22', 加油量,0)  AS DAY22
,DECODE(to_char(加油日期 ,'DD'),'23', 加油量,0)  AS DAY23
,DECODE(to_char(加油日期 ,'DD'),'24', 加油量,0)  AS DAY24
,DECODE(to_char(加油日期 ,'DD'),'25', 加油量,0)  AS DAY25
,DECODE(to_char(加油日期 ,'DD'),'26', 加油量,0)  AS DAY26
,DECODE(to_char(加油日期 ,'DD'),'27', 加油量,0)  AS DAY27
,DECODE(to_char(加油日期 ,'DD'),'28', 加油量,0)  AS DAY28
,DECODE(to_char(加油日期 ,'DD'),'29', 加油量,0)  AS DAY29
,DECODE(to_char(加油日期 ,'DD'),'30', 加油量,0)  AS DAY30
,DECODE(to_char(加油日期 ,'DD'),'31', 加油量,0)  AS DAY31
FROM TAB_LIST WHERE to_char(加油日期,'YYYYMM')='200910') A
GROUP BY A.设备

Answer 5

select 设备,sum(nvl(加油量,0)),日期
from 数据表
group by 设备,sum(加油量),日期
order by 日期 

Answer 6

with temp as
(
select 'A1' a,10 b,'2009-10-1' c from dual
union all
select 'A2',2,'2009-10-2' from dual
union all 
select 'A3',3,'2009-10-1' from dual
)
select a,"1","2","3","4","1"+"2"+"3"+"4" "5"
from
(
    select distinct a, sum(case when day(t)=1 then b else 0 end) "1",
                     sum(case when day(t)=2 then b else 0 end) "2",
                     sum(case when day(t)=3 then b else 0 end) "3" ,
                     sum(case when day(t)=4 then b else 0 end) "4"
    from
      (
           select a,(case when c=t then sum(b) else 0 end ) b,t from 
          (select a,b,to_date(c,'yyyy-mm-dd') c from temp)
          ,
          (select (to_date('2009-10','yyyy-mm')+rownum-1) t from dual connect by rownum<5)
          where to_char(c,'yyyy-mm-dd')<=to_char(t,'yyyy-mm-dd')
          group by a,c,t
      )
    group by rollup(a)
)--result:
A1 10 0 0 0 10
A2 0 2 0 0 2
A3 3 0 0 0 3
13 2 0 0 15

Answer 7

--以上写法,rownum<5  可以先判断月份的天数然后在传值过来!
--只写五天的数据,你可以自己加到三十天!

Answer 8

--给你一句判断当月天数的SQL:
SELECT  last_day(sysdate)-last_day(add_months(sysdate,-1))   FROM  DUAL

Answer 9

create or replace procedure proc(p_year in number,p_month in number)
assqlstr1 varchar2(2000);
sqlstr2 varchar2(2000);
days number;
begin
  days:=to_number(to_char(to_date(to_char(p_year)||'-'||to_char(p_month+1)||'-1','yyyy-mm-dd')-1,'dd'));
  sqlstr1:='create or replace view_test as select 设备';
  sqlstr2:='
,sum(加油量)"1-'||days||'"from (select * from tt where to_char(加油时间,''yyyymm'')='''||to_char(p_year)||to_char(p_month,'fm00')||''' union all
select ''日统计'',sum(加油量),加油日期 from tt where to_char(加油时间,''yyyymm'')='''||to_char(p_year)||to_char(p_month,'fm00')||''' group by 加油日期)group by 设备';
  for i in 1..days loop
    sqlstr1:=sqlstr1||'
,sum((decode(to_char(加油时间,''dd'')-'||i||',0,加油量))"'||i||'"';
  end loop;
--dbms_output.put_line( sqlstr1||sqlstr2);
execute immediate sqlstr1||sqlstr2;
end;

Answer 10

自己测试了一下，修正了几个小错误
create or replace procedure proc(p_year in number,p_month in number)
assqlstr1 varchar2(2000);
sqlstr2 varchar2(2000);
days number;
begin
  days:=to_number(to_char(to_date(to_char(p_year)||'-'||to_char(p_month+1)||'-1','yyyy-mm-dd')-1,'dd'));
  sqlstr1:='create or replace view view_test as select 设备';
  sqlstr2:='
,sum(加油量)"1-'||days||'" from (select * from tt where to_char(加油日期,''yyyymm'')='''||to_char(p_year)||to_char(p_month,'fm00')||''' union all
select ''日统计'',sum(加油量),加油日期 from tt where to_char(加油日期,''yyyymm'')='''||to_char(p_year)||to_char(p_month,'fm00')||''' group by 加油日期)group by 设备';
  for i in 1..days loop
    sqlstr1:=sqlstr1||'
,sum(decode(to_char(加油日期,''dd'')-'||i||',0,加油量))"'||i||'"';
  end loop;
--dbms_output.put_line( sqlstr1||sqlstr2);
execute immediate sqlstr1||sqlstr2;
end;
测试
create table tt as select 'A1' 设备,round(dbms_random.value(30,50))加油量,sysdate-20+rownum 加油日期 from dual
  connect by rownum<100;
insert into tt select 'A3' 设备,round(dbms_random.value(30,50))加油量,sysdate-20+rownum 加油日期 from dual
  connect by rownum<100;begin
proc(2009,11);
end;结果
select * from view_test;设备 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 1-30
A2 45 46 45 45 34 48 40 43 35 32 45 46 46 49 42 33 30 31 44 33 47 32 46 37 37 42 39 47 37 39 1215
A3 44 32 49 45 48 34 48 35 30 42 43 37 41 49 43 42 43 47 46 37 50 41 34 33 44 38 46 42 38 49 1250
A1 47 32 30 31 37 43 37 47 40 34 47 45 41 39 39 43 44 40 38 45 41 50 32 41 44 42 48 47 35 48 1227
日统计 136 110 124 121 119 125 125 125 105 108 135 128 128 137 124 118 117 118 128 115 138 123 112 111 125 122 133 136 110 136 3692begin
proc(2009,9);
end;select * from view_test;设备 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 1-30
A2 42 40 49 34 165
A3 39 37 35 46 157
A1 47 39 37 49 172
日统计 128 116 121 129 494

Answer 11

注意用户要有create any view的权限才能执行成功，是用户，不是角色
若没有权限，但用户本身是dba用户，给自己赋权 grant create any view to USERNAME

Answer 12

我有个问题
LAST_DAY(add_months(to_date('2009/10','yyyy/mm'),-1)) + 1
LAST_DAY(add_months(to_date('2009/10','yyyy/mm'),-1)) + 2
LAST_DAY(add_months(to_date('2009/10','yyyy/mm'),-1)) + 3
LAST_DAY(add_months(to_date('2009/10','yyyy/mm'),-1)) + 4你这个地方为什么要这样写呢？有什么好处吗
每句的后面还是要+1 、+2 、+3 、+4……加不同的值
那么和直接
to_date('2009/10/01','yyyy/mm/dd')
to_date('2009/10/02','yyyy/mm/dd')
to_date('2009/10/03','yyyy/mm/dd')
to_date('2009/10/04','yyyy/mm/dd')
有什么区别吗
谢谢指点

Answer 13

笨那
to_date('2009/10'||'/01','yyyy/mm/dd')
to_date('2009/10'||'/02','yyyy/mm/dd')
...
不就行了，不管哪个月的第一天都是1日，从第29天开始才会有所不同..

Answer 14

这是一个典型的旋转标问题，以前就有过很多关于旋转表的讨论。其实旋转表还是通过客户端（通常是报表工具）来完成做方便，用Excel就能方便的解决。就本问题，要到处所以日期和用户的源记录，可以通过以下查询SELECT b.设备, b.加油日期, sum(a.加油量) 加油量
  FROM 数据表 a, 
(SELECT 设备, myDate 加油日期
  FROM 设备表, (
 SELECT myDate
   FROM (SELECT TRUNC(sysdate, 'MON') + level - 1 myDate FROM dual CONNECT BY level <= 31)
  WHERE 日期 <= last_day(sysdate))) b
 WHERE b.设备 = a.设备 (+)
   AND b.加油日期 = a.加油日期 (+);