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

<!--StartFragment -->

表见附件

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

5个回答

前一天不存在,返回值为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]

Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问
相关内容推荐