救命啊,高手,求助关于Oracle的一个统计问题现在有这样一个表,其数据为:
-------------------------------------------------------------------------
NIAN_BS SHENGSX_BS XINGZDJ_BS JISDJ_BS XINZLC ZONGLC
2001 320101 G 2 0 74.88
2002 320101 G 2 0 84.88
2003 320101 G 2 0 84.88
2004 320101 G 2 0 299.52
-------------------------------------------------------------------------需要得到如下的结果,即将如2002-2001得到的结果放在NIAN_BS='2002'的XINZLC字段,
由于2001年之前没有相关数据,故而其XINZLC为ZONGLC值:-------------------------------------------------------------------------
NIAN_BS SHENGSX_BS XINGZDJ_BS JISDJ_BS XINZLC ZONGLC
2001 320101 G 2 74.88 74.88
2002 320101 G 2 10 84.88
2003 320101 G 2 0 84.88
2004 320101 G 2 214.64 299.52
-------------------------------------------------------------------------请问:这样的SQL怎么写啊,多谢
-------------------------------------------------------------------------
NIAN_BS SHENGSX_BS XINGZDJ_BS JISDJ_BS XINZLC ZONGLC
2001 320101 G 2 0 74.88
2002 320101 G 2 0 84.88
2003 320101 G 2 0 84.88
2004 320101 G 2 0 299.52
-------------------------------------------------------------------------需要得到如下的结果,即将如2002-2001得到的结果放在NIAN_BS='2002'的XINZLC字段,
由于2001年之前没有相关数据,故而其XINZLC为ZONGLC值:-------------------------------------------------------------------------
NIAN_BS SHENGSX_BS XINGZDJ_BS JISDJ_BS XINZLC ZONGLC
2001 320101 G 2 74.88 74.88
2002 320101 G 2 10 84.88
2003 320101 G 2 0 84.88
2004 320101 G 2 214.64 299.52
-------------------------------------------------------------------------请问:这样的SQL怎么写啊,多谢
-----------------------------------------------------------------------------
NIAN_BS SHENGSX_BS XINGZDJ_BS JISDJ_BS XINZLC ZONGLC
2001 320101 G 2 0 74.88
2002 320101 G 2 0 84.88
2003 320101 G 2 0 84.88
2004 320101 G 2 0 299.52
------------------------------------------------------------------------------需要得到如下的结果,即将如2002-2001得到的结果放在NIAN_BS='2002'的XINZLC字段,
由于2001年之前没有相关数据,故而其XINZLC为ZONGLC值:-----------------------------------------------------------------------------
NIAN_BS SHENGSX_BS XINGZDJ_BS JISDJ_BS XINZLC ZONGLC
2001 320101 G 2 74.88 74.88
2002 320101 G 2 10 84.88
2003 320101 G 2 0 84.88
2004 320101 G 2 214.64 299.52
------------------------------------------------------------------------------请问:这样的SQL怎么写啊,多谢
from table a ,table b
where a.NIAN_BS -b.NIAN_BS =1;
select * from test
NIAN_BS SHENGSX_BS XINGZDJ_BS JISDJ_BS XINZLC ZONGLC
------- ---------- ---------- -------- -------- --------
2001 320101 G 2 0.00 74.88
2002 320101 G 2 0.00 84.88
2003 320101 G 2 0.00 84.88
2004 320101 G 2 0.00 299.52
4 rows selected得出结果(oracle9i以后的数据库)
select nian_bs,shengsx_bs,xingzdj_bs,jisdj_bs,zonglc - lag_row xinzlc,zonglc
from
(
select nian_bs,shengsx_bs,xingzdj_bs,jisdj_bs,
nvl(lag(zonglc,1) over (partition by shengsx_bs order by nian_bs),0)
lag_row,zonglc
from test
)
NIAN_BS SHENGSX_BS XINGZDJ_BS JISDJ_BS XINZLC ZONGLC
------- ---------- ---------- -------- ---------- --------
2001 320101 G 2 74.88 74.88
2002 320101 G 2 10 84.88
2003 320101 G 2 0 84.88
2004 320101 G 2 214.64 299.52
4 rows selected