有关三个表查询的问题?在线等

[linefanliu]

测站名已知,用该名称到表101 STHD 站标题表中查找对应的站码 SQL语句为：

SELECT STCD FROM STHD WHERE STNM = ‘测站名称’

在表405 CSMYST中搜索年最大含沙量，及年最大出现月日 找出其中最大值及对应 年月日

SELECT MAX(YRMCS) FROM CSMYST WHERE STCD = ’站码’ （即上一语句的查询结果） 得到年最大含沙量

SELECT YRMCS,YR,YRMXMD FROM CSMYST WHERE YRMCS = 最大值（即上一语句的查询结果）;

怎么用一个查询语句查出YRMCS,YR,YRMXMD ??????

[tqqonline]

SELECT YRMCS,YR,YRMXMD
FROM CSMYST
WHERE YRMCS = ( SELECT MAX(YRMCS)
FROM CSMYST
WHERE STCD in ( SELECT STCD
FROM STHD
WHERE STNM = ‘测站名称’)
)

[hjb111]

select a.stcd,b.maxyrmcs,c.yrmcs,c.yr,c.yrmxmd
from (SELECT STCD FROM STHD WHERE STNM = ‘测站名称’) a,(SELECT MAX(YRMCS) maxyrmcs FROM CSMYST WHERE STCD = ’站码’) b,(SELECT YRMCS,YR,YRMXMD FROM CSMYST WHERE YRMCS = 最大值 ) c