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 )