请教一条统计产量的SQL语句

Question

我在做三个表的查询时:
表ComProduct            产品属性表
   wmshandleentry      产品入库表
   wmshandleoutsum   产品出库表comproduct中的字段saletypecode      销售类型,
productcode       产品编号
specificmemo      规格
GrainCode         花纹
RUBBERMARK        橡胶重量
其中产品编号决定其它属性,是和另外两张表的外键wmshandleentry字段
productcode           产品编号
Num                   入库数量
Createdate            入库日期wmshandleoutsum
productcode          产品编号
Num                  出库数量
createdate           出库日期我想得到的效果如下:
销售类型   产品编号  规格 花纹  橡胶重量 入库月累计 入库年累计  出库月累计  出库年累计
   **         **      **   **     **        **         **          **
..             ...        ... .....             ...            ....    ..我的SQL语句是这样写的:
select Comproduct.saletypecode,comproduct.productcode,comproduct.specificmemo,comproduct.GrainCode,comproduct.RUBBERMARK,sum(case when wmshandleentry.CREATEDATE between to_date('2005-11-01','yyyy-mm-dd')
and to_date('2005-11-10','yyyy-mm-dd') then wmshandleentry.NUM else 0 end) 入库月累计,sum(case when wmshandleentry.CREATEDATE between to_date('2005-01-01','yyyy-mm-dd')
and to_date('2005-11-10','yyyy-mm-dd') then wmshandleentry.NUM else 0 end) 入库年累计,sum(case when wmshandleoutsum.CREATEDATE between to_date('2005-11-01','yyyy-mm-dd')
and to_date('2005-11-10','yyyy-mm-dd') then wmshandleoutsum.NUM else 0 end) 出库月累计,sum(case when wmshandleoutsum.CREATEDATE between to_date('2005-01-01','yyyy-mm-dd')
and to_date('2005-11-10','yyyy-mm-dd') then wmshandleoutsum.NUM else 0 end) 出库年累计from comproduct,wmshandleentry,wmshandleoutsum
where comproduct.PRODUCTCODE=wmshandleentry.PRODUCTCODE And comproduct.PRODUCTCODE=wmshandleoutsum.PRODUCTCODE
group by comproduct.PRODUCTCODE,comproduct.SALETYPECODE,comproduct.SPECIFICMEMO,comproduct.GRAINCODE,comproduct.RUBBERMARK但是问题是,执行这句语句,我的机器就死在了那里,请问正确的SQL语句该怎么写?

Answer 1

SELECT A.saletypecode,A.productcode,A.specificmemo,A.GrainCode,A.RUBBERMARK
       B.入库月累计,B.入库年累计,C.出库月累计,C.出库年累计
FROM (
      SELECT productcode,
             SUM(DECODE(TO_CHAR(Createdate,'MM'),'11',Num,0)) AS 入库月累计,
             SUM(Num) AS 入库年累计
      FROM wmshandleentry
      WHERE TO_CHAR(Createdate,'YYYYMMDD') BETWEEN '20050101' AND '20051110'
      GROUP BY productcode
     )B,
     (
       SELECT productcode,
              SUM(DECODE(TO_CHAR(Createdate,'MM'),'11',Num,0)) AS 出库月累计,
              SUM(Num) AS 出库年累计
       FROM wmshandleoutsum
       WHERE TO_CHAR(Createdate,'YYYYMMDD') BETWEEN '20050101' AND '20051110'
       GROUP BY productcode
     )C,
     (
       SELECT saletypecode,productcode,specificmemo,GrainCode,RUBBERMARK
       FROM comproduct
     )A
WHERE A.productcode = B.productcode AND A.productcode = C.productcode

Answer 2

说明：入库累计月初时间：begin_month_date_1  末：last_month_date_1
      入库累计年初时间：begin_year_date_1  末：last_year_date_1
      出库累计月初时间：begin_month_date_2  末：last_month_date_2
      出库累计年初时间：begin_year_date_2  末：last_year_date_2
      入库月累计：in_month_all_num
      入库年累计：in_year_all_num
      出库月累计：out_month_all_num
      出库年累计：out_month_all_numselect a.saletypecode,a.productcode as productcode,a.specificmemo,
       a.rubber,
       sum(select num from b where createdate between begin_month_date_1  
                     and last_month_date_1) as in_month_all_num,
       sum(select num from b where createdate between begin_year_date_1  
                     and last_year_date_1) as in_year_all_num,
       sum(select num from c where createdate between begin_month_date_2  
                     and last_month_date_2) as out_month_all_num,
       sum(select num from c where createdate between begin_year_date_2 
                     and last_year_date_2) as out_year_all_num
from ComProduct a,wmshandleentry b,wmshandleoutsum c
where a.productcode=b.productcode  and  a.productcode=c.productcode;