求更完美的SQL
今天公司老大叫我写一个查询语句，需求如下：查出所有站最近时间的水位(z),站码(STCD),Ｘ坐标(LGTD)，Ｙ坐标(LTID),站名(STNM)，时间(TM).所需表结构：ST_STBPRP_B:STCDchar80STNMnvarchar300RVNMnvarchar301HNNMnvarchar301BSNMnvarchar301LGTDchar71LTTDchar61STLCnvarchar301ADDVCDchar61 ST_RIVER_R:STCDchar80TMdatetime80Zfloat81Qfloat81两表关系：ST_STBPRP_B与ST_RIVER_R通过STCD字段一对多关联，ST_STBPRP_B为主表。思考了半天，写出了如下的二种ＳＱＬ语句，虽然找出的结果符合需求，但总感觉不爽，现把ＳＱＬ语句贴出来，希望高手进来指点一二，不胜感激。 一种方案：SELECT zuo.stcd, zuo.stnm, zuo.LGTD, zuo.LTTD, zuo.tm, s_r.ZFROM ST_RIVER_R s_r right join (SELECT st.stcd, st.stnm, st.LGTD, st.LTTD, MAX(st_r.tm) as tm FROM St_River_R st_r right OUTER JOIN St_Stbprp_B st ON st_r.stcd = st.stcd GROUP BY st.stcd, st.stnm, st.LGTD, st.LTTD) zuo on s_r.tm = zuo.tm and s_r.stcd=zuo.stcd 第二种方案:select a.stcd,a.stnm,a.lgtd,a.lttd, m.tm,m.z from St_Stbprp_B a leftjoin(select x.stcd,x.z,x.tm from st_river_r x,(select st.stcd , max(st.tm) as tm from st_river_r st group by st.stcd) cwhere x.stcd=c.stcd and x.tm=c.tm) m on a.stcd=m.stcd
页:
[1]