我有这个查询：
select CPRCOSC, CPRCDCO, FCHPRDT ,CPRDTPA
from ocsacpr,
fasaorl,
fasaorh
where CPRCOSC = 'GHY02'
and fclcosc = CPRCOSC
and fclcdcm = CPRCDCO
and fclverc = CPRVER
and fclnumc = CPRNUM
and FCHORDN = FCLORDN
and FCHCOSC = FCLCOSC
它给了我：
CPRCOSC CPRCDCO FCHPRDT CPRDTPA
GHY02 2015000036 20150728 20150728
GHY02 2015000037 20150723 20150723
GHY02 2015000038 20150722 20150722
GHY02 2015000039 20150918 20150930
GHY02 2015000040 20150918 20150930
.....
GHY02 2015000041 20150731 20150731
GHY02 2015000041 20150918 20150831
GHY02 2015000041 20150921 20150930
对于最后3条记录（具有CPRCDCO），我只想选择具有FCHPRDT较高记录的（20150921在示例中）。
我该如何编辑查询？
提前致谢
您可以使用窗口函数（FIRST_VALUE），按CPRCDCO分区并按FCHPRDT（降序）进行排序。查看功能文档。
https://msdn.microsoft.com/zh-CN/library/hh213018.aspx
以下代码可以很好地工作，但是我建议您也使用INNER JOIN子句，而不要在WHERE子句中进行联接。
SELECT DISTINCT
CPRCOSC,
CPRCDCO,
FIRST_VALUE(FCHPRDT) OVER (PARTITION BY CPRCDCO ORDER BY FCHPRDT DESC) AS FCHPRDT,
FIRST_VALUE(CPRDTPA) OVER (PARTITION BY CPRCDCO ORDER BY FCHPRDT DESC) AS CPRDTPA
FROM
OCSACPR, FASAORL, FASAORH
WHERE
CPRCOSC = 'GHY02'
AND FCLCOSC = CPRCOSC
AND FCLCDCM = CPRCDCO
AND FCLVERC = CPRVER
AND FCLNUMC = CPRNUM
AND FCHORDN = FCLORDN
AND FCHCOSC = FCLCOSC
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句