1. getdate()

SELECT t1.SKCD as reservoirid,

t1.SKNM as reservoirname,

t1.RSVRTP as rsvrtp,

t6.FSLTDZ as xunxianshuiwei,

t1.LGTD AS jingdu,

t1.LTTD AS weidu,

t3.DYP AS precipitation,

t3.TM AS rainTm,

t4.RZ as waterlevel,

t4.TM as waterTm,

t4.RZ - t6.FSLTDZ as chaoxunxianshuiwei,

img.CHID AS chid,

DATEPART(yyyy,img.TM) as yearN ,

img.TM as imgTm,

t1.STLC as adress

FROM SK_BASIC_B t1

LEFT JOIN

ST_PPTN_R_V t3 ON t1.STCD = t3.STCD

LEFT JOIN

RS_RSVR_R_V t4 ON t1.STCD = t4.STCD

LEFT JOIN

sk_fcch_b t6 ON t1.SKCD = t6.SKCD

LEFT JOIN

(select a.CHID,a.TM,b.SKCD from SK_RTIMAGE_R a, SK_RTIMGCFG_B b

where a.CHID = b.CHID and ENABLED = 1

<if test="skcd != null and skcd != '' ">

and b.SKCD = #{skcd}

</if>

and ORDERINDEX = 1

and TM = (select top 1 TM from SK_RTIMAGE_R where CHID = a.CHID

<if test="dateEnd != null and dateEnd != '' ">

and #{dateEnd}>=TM

</if>

order by TM desc

)

)img ON img.SKCD=t1.SKCD

WHERE 1 = 1

<if test="skcd != null and skcd !=''">

AND t1.SKCD = #{skcd}

</if>

<if test="cityid != null and cityid != -1">

AND t1.SKCD LIKE '4205%'

</if>

<if test="reservoirname != null and reservoirname != '' ">

AND t1.SKNM LIKE '%${reservoirname}%'

</if>

order by (t4.RZ - t6.FSLTDZ) desc

2.求最大时间的数据

select a.CHID,a.TM,b.SKCD

from SK_RTIMAGE_R a, SK_RTIMGCFG_B b

where a.CHID = b.CHID and ENABLED = 1

<if test="skcd != null and skcd != '' ">

and b.SKCD = #{skcd}

</if>

and ORDERINDEX = 1

and TM = (select top 1 TM from SK_RTIMAGE_R where CHID = a.CHID

　　　　　　<if test="dateEnd != null and dateEnd != '' ">

　　　　　　and #{dateEnd}>=TM

　　　　　　</if>

　　　　order by TM desc  )