有一张表ZJB,里面有3个字段。
H_DATE, H_ID, 今日资产
2011-01-05 204 5000
2011-01-06 204 5500
2011-01-07 204 4000想用语句实现下面的样式。昨日资产用的是前一天的今日资产的值。
并且能取出一段时间的。H_DATE>='开始日期'AND H_DATE<='结束日期'
H_DATE, H_ID,今日资产, 昨日资产
2011-01-06 204 5500 5000
2011-01-07 204 4000 5500
H_DATE, H_ID, 今日资产
2011-01-05 204 5000
2011-01-06 204 5500
2011-01-07 204 4000想用语句实现下面的样式。昨日资产用的是前一天的今日资产的值。
并且能取出一段时间的。H_DATE>='开始日期'AND H_DATE<='结束日期'
H_DATE, H_ID,今日资产, 昨日资产
2011-01-06 204 5500 5000
2011-01-07 204 4000 5500
select a.*,(select 今日资产 from ZJB where H_DATE = a.H_DATE - 1)昨日资产 from ZJB a
--测试通过
H_DATE>='开始日期'AND H_DATE<='结束日期'
就可以限定日期了。。
lag(今日资产) over (partition by h_id order by h_date) 昨日资产 from zjb;
(select 今日资产[昨日资产] from zjb b
where convert(nchar(10),b.h_date,101)=convert(nchar(10),a.h_date -1,101)
and b.h_id=a.h_id
)
from zjb a
where exists (select 今日资产 from zjb c
where convert(nchar(10),c.h_date,101)=convert(nchar(10),a.h_date -1,101)
and c.h_id=a.h_id)
......
from zjb a
where exists (select 今日资产 from zjb c
where convert(nchar(10),c.h_date,101)=convert(nchar(10),a.h_date -1,101)
and c.h_id=a.h_id) and H_DATE>='开始日期'AND H_DATE<='结束日期'
SQL> with t as(
2 select '2011-01-05' h_date,204 h_id,5000 sm from dual union all
3 select '2011-01-06',204,5500 from dual union all
4 select '2011-01-07',204,4000 from dual)
5 select * from (
6 select t.*,lag(sm) over (partition by h_id order by h_date) y_sm
7 from t)
8 where y_sm is not null
9 /
H_DATE H_ID SM Y_SM
---------- ---------- ---------- ----------
2011-01-06 204 5500 5000
2011-01-07 204 4000 5500