求助，帮忙看一下这个应该怎么做

显示全部楼层 · 2005-12-25 19:51:16

一张表有3个字段：年月、货名、量
200801 a 8
200801 b 6
200801 c 9
200802 a 9
200802 b 8
200802 d 9
200803 a 4
现在需要新求一个字段：用当月的某个货的量－上个月的这个货的量，如果这个货存在，则相减，否则就为当月的量，如：
200801 a 8 .
200801 b 6 .
200801 c 9 .
200802 a 1
200802 b 8 2
200802 d 9 9
200803 a 4 -5

千问 · 2005-12-25 19:51:16

select u.ny,u.hm,u.l,decode(sign(u.l-u.l1),-1,u.l,u.l-u.l1) from (select ny,hm,l,lag(l,1) over(partition by hm order by ny) l1 from c) u
SQL> /
NY HM
L DECODE(SIGN(U.L-U.L1),-1,U.L,U.L-U.L1)
---------- ---------- ---------- --------------------------------------
200801 a
8
200802 a
9
1
200803 a
4
4
200801 b
6
200802 b
8
2
200801 c
9
200802 d
9
已选择7行。
楼主看下对不对
[ 本帖最后由 star_guan2008 于 2008-4-10 09:35 编辑 ]

千问 · 2005-12-25 19:51:16

SELECT t.*,

Nvl(qty-Lag(qty) over (PARTITION BY code ORDER BY dt),qty) AS x
FROM
(
select 200801 as dt, 'a' as code, 8 as qty from dual union all
select 200801 as dt, 'b' as code, 6 as qty from dual union all
select 200801 as dt, 'c' as code, 9 as qty from dual union all
select 200802 as dt, 'a' as code, 9 as qty from dual union all
select 200802 as dt, 'b' as code, 8 as qty from dual union all
select 200802 as dt, 'd' as code, 9 as qty from dual union all
select 200803 as dt, 'a' as code, 4 as qty from dual
) t
/
DT
CODE
QTY
X
200801
a
8
8
200802
a
9
1
200803
a
4
-5
200801
b
6
6
200802
b
8
2
200801
c
9
9
200802
d
9
9

千问 · 2005-12-25 19:51:16

SQL> select * from tmp_11;
DT CODEQTY
---------- ---- ----------
200801 a
8
200801 b
6
200801 c
9
200802 a
9
200802 b
8
200802 d
9
200803 a
4
7 rows selected
SQL>
SQL> select DT,
2 CODE,
3 QTY,
4 QTY - lag(QTY, 1, 0) over(partition by CODE order by DT) NEW_QTY
5from tmp_11
6order by DT, CODE;
DT CODEQTYNEW_QTY
---------- ---- ---------- ----------
200801 a
8
8
200801 b
6
6
200801 c
9
9
200802 a
9
1
200802 b
8
2
200802 d
9
9
200803 a
4 -5
7 rows selected
SQL>

千问 · 2005-12-25 19:51:16

原帖由 bell6248 于 2008-4-10 10:03 发表

SQL> select * from tmp_11;
DT CODEQTY
---------- ---- ----------
200801 a
8
200801 b
6
200801 c
9
200802 a
9
200802 b
8
200802 d
9
200803 a
4
7 rows selected
SQL>
SQL> select DT,
2 CODE,
3 QTY,
4 QTY - lag(QTY, 1, 0) over(partition by CODE order by DT) NEW_QTY
5from tmp_11
6order by DT, CODE;
DT CODEQTYNEW_QTY
---------- ---- ---------- ----------
200801 a
8
8
200801 b
6
6
200801 c
9
9
200802 a
9
1
200802 b
8
2
200802 d
9
9
200803 a
4 -5
7 rows selected
SQL>
如果这条记录没有对不对?
200802 a
9

千问 · 2005-12-25 19:51:16

拿纯sql能做吗？

千问 · 2005-12-25 19:51:16

谢了，楼上的几位