SQL 大挑战-- 可以放到精华区的几个命题

biti_rainy

这个我说不好了
差异太大

I will try the new analistic functions avaiable in 8i.  You can easily translate a column from number to date.

hr@TEST920.WORLD>create table t
  2  ( a number not null,
  3    income  number,
  4    expense number
  5  )  pctfree 0 nologging
  6  /

Table created.

hr@TEST920.WORLD>
  1  insert into t
  2      select trunc(dbms_random.value(1,20)), round(dbms_random.value(100,10000),2),round(dbms_random.value(100,10000),2)
  3      from all_objects
  4*     where rownum <= 10
  5  /

10 rows created.

hr@TEST920.WORLD>commit;

Commit complete.

hr@TEST920.WORLD>select * from t;

         A     INCOME    EXPENSE
---------- ---------- ----------
        12    1010.29    1747.35
        17    7495.49    1892.23
         3    3129.15    9190.91
        10    7148.18     336.46
        15     1490.9     459.25
        10     7852.9    8493.48
         2    8115.83    2149.76
         8    7962.83    3464.67
         9    3178.35    8983.54
         5    2737.83    3524.53

10 rows selected.
hr@TEST920.WORLD>
  1  select a,sum(income) income, sum(expense) expense ,balance
  2  from (
  3  select a, income,expense,income - expense,sum(gap) over (order by a rows between unbounded preceding and current row) balance
  4  from
  5   ( select a,income,expense, income-expense as gap from t
  6      union all
  7      select minval + rownum -1 as  a, 0 income, 0 expense, 0 gap
  8      from
  9      all_objects t1,
10      ( select min(a) minval,max(a) maxval
11        from t ) t2
12      where rownum <= maxval - minval - 1
13    )
14* ) group by a,balance
hr@TEST920.WORLD>/

         A     INCOME    EXPENSE    BALANCE
---------- ---------- ---------- ----------
         2    8115.83    2149.76    5966.07
         3    3129.15    9190.91     -95.69
         4          0          0     -95.69
         5    2737.83    3524.53    -882.39
         6          0          0    -882.39
         7          0          0    -882.39
         8    7962.83    3464.67    3615.77
         9    3178.35    8983.54   -2189.42
        10     7852.9    8493.48    3981.72
        10    7148.18     336.46     4622.3
        11          0          0    3981.72
        12    1010.29    1747.35    3244.66
        13          0          0    3244.66
        14          0          0    3244.66
        15     1490.9     459.25    4276.31
        17    7495.49    1892.23    9879.57

16 rows selected.

And I am not sure wether it is ok to code with all_objects included, since the record of all_objects may run out comparing to the requirement from table a. I use all_objects a lot in test eviroment, but I try to conquer it in production. Any coments are welcome.

biti_rainy

hahaer

我觉得 expert one  on  one 上关于分析函数讲的最好  

不过上面的这个是缺省的，所以我就没有写了，我觉得写了更让人迷糊

呵呵，你也有EXPORT ONE TO ONE。真是哥们

biti_rainy

不如 overtime 的哪个扭的好看  

BTW: 给你发了个 PM

darkstorm

根据biti_rainy的all_objects（他那个logo总让我想入非非）的方法，我会整理出一个答案给大家参考。

希望还有其它方法，因为一旦是要处理好几万天的数据，all_objects就不够用了---
不过那时为此写个动态生成的sql就可以了。不必要非要用select

biti_rainy

select ...  from  all_objects,all_objects where rownum < ?
这样足够了  

darkstorm

为方便阅读故，复杂的SQL被写成view
一、建立一个表：
SQL> desc test2
名称                                      是否为空? 类型
----------------------------------------- -------- -----------
RQ                                                 DATE
INCOME                                             NUMBER(9)
OUTCOME                                            NUMBER(9

二、插入三笔数据
RQ             INCOME    OUTCOME
---------- ---------- ----------
01-3月 -00         50         30
02-3月 -00         45         60
05-3月 -00         60         10

三、第一个答案
create view result1(seq,rq,income,outcome,balance) as
select t1.row1,t1.rq,t1.income,t1.outcome,sum(t2.dif2) from
(select rownum row1,rq,income,outcome,income-outcome dif1 from test2) t1,
(select rownum row2,rq,income-outcome dif2  from test2) t2
where t1.row1 >= t2.row2
group by t1.row1,t1.rq,t1.income,t1.outcome

这个view就是答案

四、第二个答案
首先，插入一笔数据
RQ             INCOME    OUTCOME
---------- ---------- ----------
05-3月 -00         60         10

然后
create view result2(rq,income,outcome,balance) as
select t3.rq,sum(t3.income),sum(t3.outcome),max(dif3) from
(
select t1.row1,t1.rq,t1.income,t1.outcome,sum(t2.dif2) dif3 from
(select rownum row1,rq,income,outcome,income-outcome dif1 from test2) t1,
(select rownum row2,rq,income-outcome dif2  from test2) t2
where t1.row1 >= t2.row2
group by t1.row1,t1.rq,t1.income,t1.outcome
) t3
group by t3.rq

就是答案二

五、第三个答案
先建立一个可以生成连续日期序列的view
create view test2_date_seq as
select (select min(rq) from test2)+rownum-1 rq from all_objects where rownum<=(select max(rq)-min(rq)+1 from test2)
再建立一个view
create or replace view test2_ext as
select t2.rq,nvl(t1.income,0) income,nvl(t1.outcome,0) outcome from test2 t1,test2_date_seq t2
where t2.rq=t1.rq(+)

再建立地三个view，也是答案
create view result3(rq,income,outcome,balance) as
select t3.rq,sum(t3.income),sum(t3.outcome),max(dif3) from
(
select t1.row1,t1.rq,t1.income,t1.outcome,sum(t2.dif2) dif3 from
(select rownum row1,rq,income,outcome,income-outcome dif1 from test2_ext) t1,
(select rownum row2,rq,income-outcome dif2  from test2_ext) t2
where t1.row1 >= t2.row2
group by t1.row1,t1.rq,t1.income,t1.outcome
) t3
group by t3.rq

nanquanc

我的方法
现有表 sum
如下
M       CNT      INER      OUER
- --------- --------- ---------
1         1         5         5
2         6        15         5
3         1        10         5
4         1        10         5
5         1        40         5

SQL>  select c.month,
                      SUM(C.INER),
                      SUM(C.OUER),
                     (E.INER-E.OUER)
              from  (select a.month,
                                 sum(a.cnt),
                                 sum(b.cnt) cnt_1,
                                 SUM(B.INER) INER ,
                                 SUm(B.OUER) OUER
                         from sum a,
                                 (select  sum.
                                             month,
                                             sum(sum.cnt) cnt,
                                               SUM(INER) INER,
                                            SUM(OUER) OUER
                                   from sum
                                   group by sum.month) b
     where a.month>=b.month
     group by a.month) e,
     sum c
     where c.month=e.month
     group by c.month,e. cnt_1,(E.INER-E.OUER)
0  /

M SUM(C.INER) SUM(C.OUER) (E.INER-E.OUER)
- ----------- ----------- ---------------
1           5           5               0
2          15           5              10
3          10           5              15
4          10           5              20
5          40           5              55

biti_rainy

如果你的 M  这个字段的值不是连续的，你这个查询能解决么？