请教一条SQL

Question

画面机能要求对一张表的数据进行统计
表定义（略去用不到的字段）
LB01_PURRECEIVEBOOK
（
SUPPLIERCD          VARCHAR2，
PURRECEIVEDATE    DATE，         --format: 2009/11/01
RECEIVEAMT         NUMBER
）
① 按年统计时，如果出现空年，则做成该年度金额为0
例：
1999    2009/09/01    1,000
1999    2009/11/01    3,000
1999    2007/12/01    2,000
抽出：
1999    2009    4,000
1999    2008    0
1999    2007    2,000
②按月统计时，出现空月，则做成该月份金额为0
1999    2009/09/01    1,000
1999    2009/11/01    3,000
1999    2009/12/01    2,000
抽出：
1999    2009/09    1,000
1999    2009/10    0
1999    2009/11    3,000
1999    2009/12    2,000
这个可以比较简单的作出来吗？

Answer 1

1.
with t as(select * from
  (select distinct SUPPLIERCD from LB01_PURRECEIVEBOOK),
  (select (select add_months(trunc(min(PURRECEIVEDATE),'yyyy') from LB01_PURRECEIVEBOOK),12*(rownum-1)) year
    from all_objects where rownum<=(select months_between(trunc(max(PURRECEIVEDATE),'yyyy'),trunc(min(PURRECEIVEDATE),'yyyy'))/12+1 from LB01_PURRECEIVEBOOK))
select t.SUPPLIERCD,t.year,nvl(sum(a.RECEIVEAMT),0)
from t,LB01_PURRECEIVEBOOK a
where t.SUPPLIERCD=a.SUPPLIERCD(+)
  and t.year=trunc(a.PURRECEIVEDATE,'yyyy')(+)
group by t.SUPPLIERCD,t.year
2.select t.SUPPLIERCD,t.month,nvl(sum(a.RECEIVEAMT),0)
from (select * from
  (select distinct SUPPLIERCD from LB01_PURRECEIVEBOOK),
  (select (select add_months(trunc(min(PURRECEIVEDATE),'mm') from
   LB01_PURRECEIVEBOOK),rownum-1) month
    from all_objects 
  where rownum<=(select months_between(trunc(max(PURRECEIVEDATE),'mm'),trunc(min(PURRECEIVEDATE),'mm'))+1 from LB01_PURRECEIVEBOOK))t,
LB01_PURRECEIVEBOOK a
where t.SUPPLIERCD=a.SUPPLIERCD(+)
  and t.year=trunc(a.PURRECEIVEDATE,'mm')(+)
group by t.SUPPLIERCD,t.month

Answer 2

狼 请问with t as是什么意思啊，看你用的挺多的

Answer 3

 (select (select add_months(trunc(min(PURRECEIVEDATE),'mm') from
改成
 (select add_months((select trunc(min(PURRECEIVEDATE),'mm') from

Answer 4

对不起，怎么报错误
ORA-00909 invalid number of arguments 

Answer 5

with t as 表t本来不存在，自己定义一些数据，然后拿t当表用。

Answer 6

有点错误，修正下
1.
with t as(select * from
  (select distinct SUPPLIERCD from LB01_PURRECEIVEBOOK),
  (select add_months((select trunc(min(PURRECEIVEDATE),'yyyy') from LB01_PURRECEIVEBOOK),12*(rownum-1)) year
    from all_objects where rownum<=(select months_between(trunc(max(PURRECEIVEDATE),'yyyy'),trunc(min(PURRECEIVEDATE),'yyyy'))/12+1 from LB01_PURRECEIVEBOOK)))
select t.SUPPLIERCD,t.year,nvl(sum(a.RECEIVEAMT),0)
from t,LB01_PURRECEIVEBOOK a
where t.SUPPLIERCD=a.SUPPLIERCD(+)
  and t.year=trunc(a.PURRECEIVEDATE(+),'yyyy')
group by t.SUPPLIERCD,t.year
2.
with t as(select * from
  (select distinct SUPPLIERCD from LB01_PURRECEIVEBOOK),
  (select add_months((select trunc(min(PURRECEIVEDATE),'mm') from LB01_PURRECEIVEBOOK),rownum-1) month
    from all_objects where rownum<=(select months_between(trunc(max(PURRECEIVEDATE),'mm'),trunc(min(PURRECEIVEDATE),'mm'))+1 from LB01_PURRECEIVEBOOK)))
select t.SUPPLIERCD,t.month,nvl(sum(a.RECEIVEAMT),0)
from t,LB01_PURRECEIVEBOOK a
where t.SUPPLIERCD=a.SUPPLIERCD(+)
  and t.month=trunc(a.PURRECEIVEDATE(+),'mm')
group by t.SUPPLIERCD,t.month
order by 1,2

Answer 7

with as用来生成虚拟的表，用于后面的查询，特别在需要多次使用这个结果的时候比较方便
这里可以将with as后面的写到from 后面
这个题其实主要麻烦在：如果出现空年/月，则做成该年度/月金额为0
因此要增加一个表包含所有的SUPPLIERCD和年/月，来和原表进行关联查询

Answer 8

sql1:with LB01_PURRECEIVEBOOK as(
select '1999' SUPPLIERCD,to_date('2009/09/01','yyyy/mm/dd') PURRECEIVEDATE,1000 RECEIVEAMT from dual
union all
select '1999' SUPPLIERCD,to_date('2009/11/01','yyyy/mm/dd') PURRECEIVEDATE,3000 RECEIVEAMT from dual
union all
select '1999' SUPPLIERCD,to_date('2007/12/01','yyyy/mm/dd') PURRECEIVEDATE,2000 RECEIVEAMT from dual
)
select a.SUPPLIERCD,year,nvl(rt,0) from
(
select SUPPLIERCD,year from 
(select distinct SUPPLIERCD from LB01_PURRECEIVEBOOK) lk,
(select to_char(add_months((select trunc(min(PURRECEIVEDATE),'yyyy') from 
       LB01_PURRECEIVEBOOK),12*(rownum-1)),'yyyy') year
    from all_objects where rownum<=(select months_between(trunc(max(PURRECEIVEDATE),'yyyy'),
    trunc(min(PURRECEIVEDATE),'yyyy'))/12+1 from LB01_PURRECEIVEBOOK)
   ) tp
) a,  
(
select SUPPLIERCD,to_char(PURRECEIVEDATE,'yyyy') y,sum(RECEIVEAMT) rt from LB01_PURRECEIVEBOOK
 group by SUPPLIERCD,to_char(PURRECEIVEDATE,'yyyy')
 ) c where a.SUPPLIERCD = c.SUPPLIERCD(+) and year = y(+)sql2:
with LB01_PURRECEIVEBOOK as(
select '1999' SUPPLIERCD,to_date('2009/09/01','yyyy/mm/dd') PURRECEIVEDATE,1000 RECEIVEAMT from dual
union all
select '1999' SUPPLIERCD,to_date('2009/11/01','yyyy/mm/dd') PURRECEIVEDATE,3000 RECEIVEAMT from dual
union all
select '1999' SUPPLIERCD,to_date('2007/12/01','yyyy/mm/dd') PURRECEIVEDATE,2000 RECEIVEAMT from dual
)
select a.SUPPLIERCD,ym,nvl(rt,0) from
(
select SUPPLIERCD,ym from 
(select distinct SUPPLIERCD from LB01_PURRECEIVEBOOK) lk,
(select to_char(add_months((select trunc(min(PURRECEIVEDATE),'mm') from LB01_PURRECEIVEBOOK),rownum-1),'yyyy-mm') ym
    from all_objects where rownum<=(select months_between(trunc(max(PURRECEIVEDATE),'mm'),
         trunc(min(PURRECEIVEDATE),'mm'))+1 from LB01_PURRECEIVEBOOK))) a,
(
select SUPPLIERCD,to_char(PURRECEIVEDATE,'yyyy-mm') y,sum(RECEIVEAMT) rt from LB01_PURRECEIVEBOOK
 group by SUPPLIERCD,to_char(PURRECEIVEDATE,'yyyy-mm')
 ) c where a.SUPPLIERCD = c.SUPPLIERCD(+) and ym = y(+)

Answer 9

整理一下，如下：
① 按年统计时，如果出现空年，则做成该年度金额为0 
with LB01_PURRECEIVEBOOK as(
select '1999' SUPPLIERCD,to_date('2009/09/01','yyyy/mm/dd') PURRECEIVEDATE,1000 RECEIVEAMT from dual
union all
select '1999' SUPPLIERCD,to_date('2009/11/01','yyyy/mm/dd') PURRECEIVEDATE,3000 RECEIVEAMT from dual
union all
select '1999' SUPPLIERCD,to_date('2007/12/01','yyyy/mm/dd') PURRECEIVEDATE,2000 RECEIVEAMT from dual
)
select a.SUPPLIERCD,year,nvl(rt,0) from 
(select SUPPLIERCD,year from 
(select distinct SUPPLIERCD from LB01_PURRECEIVEBOOK) lk, 
(select to_char(add_months((select trunc(min(PURRECEIVEDATE),'yyyy') from 
      LB01_PURRECEIVEBOOK),12*(rownum-1)),'yyyy') year 
    from all_objects where rownum <=(select months_between(trunc(max(PURRECEIVEDATE),'yyyy'), 
    trunc(min(PURRECEIVEDATE),'yyyy'))/12+1 from LB01_PURRECEIVEBOOK)) tp) a,   
(select SUPPLIERCD,to_char(PURRECEIVEDATE,'yyyy') y,sum(RECEIVEAMT) rt from LB01_PURRECEIVEBOOK 
group by SUPPLIERCD,to_char(PURRECEIVEDATE,'yyyy') ) c where a.SUPPLIERCD = c.SUPPLIERCD(+) and year = y(+) 
②按月统计时，出现空月，则做成该月份金额为0
整理如下：sql2with LB01_PURRECEIVEBOOK as(
select '1999' SUPPLIERCD,to_date('2009/09/01','yyyy/mm/dd') PURRECEIVEDATE,1000 RECEIVEAMT from dual
union all
select '1999' SUPPLIERCD,to_date('2009/11/01','yyyy/mm/dd') PURRECEIVEDATE,3000 RECEIVEAMT from dual
union all
select '1999' SUPPLIERCD,to_date('2007/12/01','yyyy/mm/dd') PURRECEIVEDATE,2000 RECEIVEAMT from dual
)
select a.SUPPLIERCD,ym,nvl(rt,0) from
(select SUPPLIERCD,ym from 
(select distinct SUPPLIERCD from LB01_PURRECEIVEBOOK) lk,
(select to_char(add_months((select trunc(min(PURRECEIVEDATE),'mm') from LB01_PURRECEIVEBOOK),rownum-1),'yyyy-mm') ym
    from all_objects where rownum<=(select months_between(trunc(max(PURRECEIVEDATE),'mm'),trunc(min(PURRECEIVEDATE),'mm'))+1 from LB01_PURRECEIVEBOOK))) a,
(select SUPPLIERCD,to_char(PURRECEIVEDATE,'yyyy-mm') y,sum(RECEIVEAMT) rt from LB01_PURRECEIVEBOOK
 group by SUPPLIERCD,to_char(PURRECEIVEDATE,'yyyy-mm')) c where a.SUPPLIERCD = c.SUPPLIERCD(+) and ym = y(+)