日期:2014-05-17  浏览次数:21049 次

Oracle如何实现联查的TOP1
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)那里如何实现.谢谢指点

------解决方案--------------------
SQL code

--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 t.*,rownum from (select * from s_biz_entity order by ywst_id) t where rownum=1
先把你的表按你的规则排序后再将它作为一张表查询 试试
------解决方案--------------------
ORACLE中用分页可以实现
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 ......