--試試以下: SELECT b.STCD,b.STNM ,c.*,d.* FROM ST_STBPRP_B b CROSS APPLY ( SELECT TOP 1 RZ hightez,TM hztm FROM ST_RSVR_R WHERE STCD = b.STCD AND MONTH(r.tm) = 8 AND YEAR(r.tm) = 2014 ORDER BY RZ DESC,TM DESC ) c CROSS APPLY ( SELECT TOP 1 RZ hightez,TM lztm FROM ST_RSVR_R WHERE STCD = b.STCD AND MONTH(r.tm) = 8 AND YEAR(r.tm) = 2014 ORDER BY RZ ASC,TM DESC ) d
基本就是上面的图的样子,下面是 SQL select b.STNM,r.STCD,r.TM,r.RZ from ST_RSVR_R r , ST_STBPRP_B b where b.STCD=r.STCD
--修正如下: SELECT b.STCD,b.STNM ,c.*,d.* FROM ST_STBPRP_B b CROSS APPLY ( SELECT TOP 1 RZ hightez,TM hztm FROM ST_RSVR_R WHERE STCD = b.STCD AND MONTH(tm) = 8 AND YEAR(tm) = 2014 ORDER BY RZ DESC,TM DESC ) c CROSS APPLY ( SELECT TOP 1 RZ hightez,TM lztm FROM ST_RSVR_R WHERE STCD = b.STCD AND MONTH(tm) = 8 AND YEAR(tm) = 2014 ORDER BY RZ ASC,TM DESC ) d
卻只見一個表的結構?
ST_STBPRP_B 表示关联测站名称的,这个无所谓的,我只要求出最大值所对应的时间就行了。
ST_STBPRP_B表可以无视。
--試試以下:
SELECT b.STCD,b.STNM ,c.*,d.*
FROM ST_STBPRP_B b
CROSS APPLY
(
SELECT TOP 1 RZ hightez,TM hztm
FROM ST_RSVR_R
WHERE STCD = b.STCD AND MONTH(r.tm) = 8 AND YEAR(r.tm) = 2014
ORDER BY RZ DESC,TM DESC
) c
CROSS APPLY
(
SELECT TOP 1 RZ hightez,TM lztm
FROM ST_RSVR_R
WHERE STCD = b.STCD AND MONTH(r.tm) = 8 AND YEAR(r.tm) = 2014
ORDER BY RZ ASC,TM DESC
) d
select b.STNM,r.STCD,r.TM,r.RZ from ST_RSVR_R r , ST_STBPRP_B b where b.STCD=r.STCD
--修正如下:
SELECT b.STCD,b.STNM ,c.*,d.*
FROM ST_STBPRP_B b
CROSS APPLY
(
SELECT TOP 1 RZ hightez,TM hztm
FROM ST_RSVR_R
WHERE STCD = b.STCD AND MONTH(tm) = 8 AND YEAR(tm) = 2014
ORDER BY RZ DESC,TM DESC
) c
CROSS APPLY
(
SELECT TOP 1 RZ hightez,TM lztm
FROM ST_RSVR_R
WHERE STCD = b.STCD AND MONTH(tm) = 8 AND YEAR(tm) = 2014
ORDER BY RZ ASC,TM DESC
) d