sql检索问题

Question

数据表如下所示
仓库名    时间起       时间止      起止时间内每天进货量
1         20080101     20080104            5
1         20080103     20080106            5
2         20080101     20080104            5
2         20080103     20080106            5要得到的检索结果如下日期              仓库1 进货量               仓库2进货量
20080101                5                          5
20080102                5                          5
20080103                10                         10
20080104                10                         10
20080105                 5                          5
20080106                 5                          5请大家帮忙看看改怎么写sql啊，谢谢啦！

Answer 1

试一下，没测试，就是一个思路
select to_date('20080101','yyyymmdd')+rownum-1 as 日期,
       (select sum(起止时间内每天进货量) from table where 仓库名=1 and to_date('20080101','yyyymmdd')+rownum-1=>时间起 and to_date('20080101','yyyymmdd')+rownum-1<=时间止) as 仓库1 进货量,
       (select sum(起止时间内每天进货量) from table where 仓库名=2 and to_date('20080101','yyyymmdd')+rownum-1=>时间起 and to_date('20080101','yyyymmdd')+rownum-1<=时间止) as 仓库2 进货量from '一个数据超多的表'
where to_date('20080101','yyyymmdd')+rownum-1<='20080106'

Answer 2

TRY IT ..
SQL> SELECT * FROM TEST_DW;      DWID STARTTIME   ENDTIME         AMOUNT
---------- ----------- ----------- ----------
         1 1/1/2008    1/4/2008             5
         1 1/3/2008    1/6/2008             5
         2 1/1/2008    1/4/2008             5
         2 1/3/2008    1/6/2008             5
         1 1/7/2008    1/12/2008            6
         2 1/7/2008    1/11/2008            86 rows selectedSQL> 
SQL> SELECT STARTTIME + RN "NEW_DATE",
  2         SUM(DECODE(DWID, 1, AMOUNT, 0)) "DW_1",
  3         SUM(DECODE(DWID, 2, AMOUNT, 0)) "DW_2"
  4    FROM TEST_DW,
  5         (SELECT ROWNUM - 1 RN
  6            FROM ALL_OBJECTS
  7           WHERE ROWNUM <=
  8                 (SELECT MAX(TO_NUMBER(ENDTIME - STARTTIME)) FROM TEST_DW) + 1)
  9   WHERE STARTTIME < ENDTIME - RN + 1
 10   GROUP BY STARTTIME + RN;NEW_DATE          DW_1       DW_2
----------- ---------- ----------
1/1/2008             5          5
1/2/2008             5          5
1/3/2008            10         10
1/4/2008            10         10
1/5/2008             5          5
1/6/2008             5          5
1/7/2008             6          8
1/8/2008             6          8
1/9/2008             6          8
1/10/2008            6          8
1/11/2008            6          8
1/12/2008            6          012 rows selectedSQL> 

Answer 3

DROP TABLE ck;
CREATE TABLE ck(NAME INT,ST DATE,et DATE,num INT,ID INT);
--仓库名    时间起      时间止      起止时间内每天进货量 
insert INTO ck VALUES(1,to_date('20080101','yyyymmdd'),to_date('20080104','yyyymmdd'),5,1); 
insert into ck VALUES(1,to_date('20080103','yyyymmdd'),to_date('20080106','yyyymmdd'),5,2); 
insert into ck VALUES(2,to_date('20080101','yyyymmdd'),to_date('20080104','yyyymmdd'),5,3); 
insert into ck VALUES(2,to_date('20080103','yyyymmdd'),to_date('20080106','yyyymmdd'),5,4); 
--要得到的检索结果如下 
/*--日期              仓库1 进货量              仓库2进货量 
20080101                5                          5 
20080102                5                          5 
20080103                10                        10 
20080104                10                        10 
20080105                5                          5 
20080106                5                          5 
*/SELECT DD 日期, MAX(DECODE(NAME, 1, SNUM)) 仓库1, MAX(DECODE(NAME, 2, SNUM)) 仓库2
  FROM (SELECT DD, NAME, SUM(NUM) SNUM
          FROM (SELECT CK.ST + RN - 1 DD, CK.NAME, CK.NUM, ID
                  FROM CK,
                       (SELECT ROWNUM RN
                          FROM DUAL
                        CONNECT BY ROWNUM <= (SELECT MAX(ET - ST + 1) FROM CK))
                 WHERE CK.ST + RN - 1 BETWEEN ST AND ET)
         GROUP BY DD, NAME)
 GROUP BY DD
 ORDER BY 1;
输出:
日期     仓库1 仓库2
2008-01-01 5     5
2008-01-02 5     5
2008-01-03 10   10
2008-01-04 10   10
2008-01-05 5     5
2008-01-06 5     5