按日期和组查询某一列的增量值 SQL语句

<!--StartFragment -->

表见附件

TJ-VALUE为每天的统计量，JLD_DM为每个计量点的分组情况。TJ_DATA为日期。增量值=某天的TJ_VALUE--某天前一天的TJ_VALUE.
现在要按组查出某天（页面提交的日期，假如为date）的增量值，如果某天的前一天不存在最好有个提示。 

前一天不存在，返回值为0
[code="sql"]
select (case when TJ_VALUE_PRE is not null
then TJ_VALUE-TJ_VALUE_PRE
else 0
end)

from (select TJ_VALUE from TJ where TJ_DATE='20110102') tj1,

(select TJ_VALUE as TJ_VALUE_PRE form TJ where TJ_DATE=

to_char((to_date(20110102,'yyyymmdd')-1),'yyyymmdd')

) tj2

where TJ1.JLD_DM = TJ2.JLD_DM(+)
[/code]

[code="sql"]
select TJ_VALUE-TJ_VALUE_PRE
from (select TJ_VALUE from TJ where TJ_DATE=20110102) tj1,
(select TJ_VALUE as TJ_VALUE_PRE form TJ where TJ_DATE=20110101) tj2
where TJ1.JLD_DM = TJ2.JLD_DM
[/code]
沙发。

如果date＝20110102
[code="sql"]
select TJ_VALUE-TJ_VALUE_PRE

from (select TJ_VALUE from TJ where TJ_DATE=date) tj1,

(select TJ_VALUE as TJ_VALUE_PRE form TJ where TJ_DATE=
to_char((to_date(date,'yyyymmdd')-1),'yyyymmdd')
) tj2

where TJ1.JLD_DM = TJ2.JLD_DM
[/code]
板凳

这么写清晰点。
[code="sql"]
select TJ_VALUE-TJ_VALUE_PRE

from (select TJ_VALUE from TJ where TJ_DATE='20110102') tj1,

(select TJ_VALUE as TJ_VALUE_PRE form TJ where TJ_DATE=

to_char((to_date(20110102,'yyyymmdd')-1),'yyyymmdd')

) tj2

where TJ1.JLD_DM = TJ2.JLD_DM
[/code]

漏了一个字段。
[code="sql"]
select (case when TJ_VALUE_PRE is not null

then TJ_VALUE-TJ_VALUE_PRE

else 0

end)

from (select JLD_DM, TJ_VALUE from TJ where TJ_DATE='20110102') tj1,

(select JLD_DM, TJ_VALUE as TJ_VALUE_PRE form TJ where TJ_DATE=

to_char((to_date(20110102,'yyyymmdd')-1),'yyyymmdd')

) tj2

where TJ1.JLD_DM = TJ2.JLD_DM(+)
[/code]