oracle 查询数据只要排序后的第一条记录（不用rownum=1），sql语句怎么写

Question

SELECT field1, field2, field3... fieldn
FROM (SELECT field1, field2, field3...fieldn, ROW_NUMBER() OVER(ORDER BY field1 DESC) AS RK ) FROM tab ) t
WHERE rk = 1 
把这个语句嵌套到别的语句里面后
select t.a,t.b,
(SELECT field1, field2, field3... fieldn
FROM (SELECT field1, field2, field3...fieldn, ROW_NUMBER() OVER(ORDER BY field1 DESC) AS RK ) FROM tab where field1=t.a) t
WHERE rk = 1) 
from lkk t;
会报错 ora-00904 where field1=t.a) "T"."A"请教高手怎么改

Answer 1

假设排序的字段名字为field1降序, table name: tabSELECT field1, field2, field3... fieldnFROM (SELECT field1, field2, field3...fieldn, ROW_NUMBER() OVER(ORDER BY field1 DESC) AS RK ) FROM tab ) tWHERE rk = 100904是说列名不正确，仔细看了下，AS RK后面多了一个括号，下面这样就可以了SELECT field1, field2, field3... fieldnFROM (SELECT field1, field2, field3...fieldn,ROW_NUMBER() OVER(ORDER BY field1 DESC) AS RKFROM tabwhere field1=表名.字段名) tWHERE rk = 1我给你改一下吧，你写的那个效率很低的SELECT t1.a, t1.b, t2.field1, t2.field2, t2.field3...t2.fieldnFROM lkk t1,(SELECT field1, field2, field3...fieldn,ROW_NUMBER() OVER(ORDER BY field1 DESC) AS RKFROM tab) tWHERE t.field1 = t1.aAND t.rk = 1

Answer 2

SELECT field1, field2, field3... fieldnFROM (SELECT field1, field2, field3...fieldn, ROW_NUMBER() OVER(ORDER BY field1 DESC) AS RK ) FROM tab ) tWHERE rk = 1把这个语句嵌套到别的语句里面后select t.a,t.b,(SELECT field1, field2, field3... fieldnFROM (SELECT field1, field2, field3...fieldn, ROW_NUMBER() OVER(ORDER BY field1 DESC) AS RK ) FROM tab where field1=t.a) tWHERE rk = 1)from lkk t;会报错 ora-00904 where field1=t.a)

Answer 3

先在 XSREMOBILE 表上建 SENDDATE和ID&nbsp;的复合索引.把 WHERE ROWNUM < 50 + 1改为 WHERE ROWNUM <= 50应该可以快些.SELECT /*+ FIRST_ROWS */ *FROM (SELECT /*+ parallel(M,10) */ *FROM XSREMOBILE MWHERE M.SENDDATE >= 20101126AND M.SENDDATE <= 20101126oRDER BY M.ID&nbsp;DESC)WHERE ROWNUM <= 50