救命啊,高手,求助关于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怎么写啊,多谢
解决方案 »
- 各位大人:如何合并两张表
- 查看job上次执行了多长时间
- No error information available: DB_E_BADACCESSORTYPE(0x80040E4B).
- 怎么用SQL语句判断字符串????
- 求一SQL语句?
- 问几个SQL命令
- oracle如何得到用户创建的表名
- 有关 imp 导入时怎样替换现有的表呢?
- 如何将这条SQL的update语句改成Oracle的语句(同时修改多字段)?
- 在linux72下安装oracle9i,停留在init java virtual machine,求救
- PL/SQL 里面,右健点击存储过程,可以发现一个 recompile 命令,请问这个命令应该如何在语句中实现?
- 多表查询中rownum的使用问题
-----------------------------------------------------------------------------
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