SELECT
field1,
(SELECT TOP(1) Id FROM TB_GongWenLz WHERE TB_GongWenLz.GongwenId=gw.Id ORDER BY Tid DESC) AS field2,
field3
FROM TB_GongWen gw
WHERE ......请问这个SQL语句如何改成Oracle的,主要是field2的TOP(1)那里如何实现.谢谢指点
field1,
(SELECT TOP(1) Id FROM TB_GongWenLz WHERE TB_GongWenLz.GongwenId=gw.Id ORDER BY Tid DESC) AS field2,
field3
FROM TB_GongWen gw
WHERE ......请问这个SQL语句如何改成Oracle的,主要是field2的TOP(1)那里如何实现.谢谢指点
--oracle没top,一般都用rownum或rank等,试下
select field1,t.Id,field3
from TB_GongWen,
(select rank() over(partition by GongwenId order by Tid desc) rank,Id,GongwenId FROM TB_GongWenLz) t
where t.rank=1 and t.GongwenId=gw.Id
...
先把你的表按你的规则排序后再将它作为一张表查询 试试
SELECT
field1,
select id from(SELECT Id,rownum FROM TB_GongWenLz
WHERE TB_GongWenLz.GongwenId=gw.Id
ORDER BY Tid DESC) field2 where rownum=1,
field3
FROM TB_GongWen gw
WHERE ......
大哥,你这段SQL代码的话,会提示无法识别gw的.如果这样写就行的话,我就不用在这提问咯.我就是纠结在这个问题上...
最近项目在SQL转ORACLE,所以还是不想大改SQL的,谢谢了哈.