SQL查询完整扫描两次时出现问题？

发布于 2024-11-07 21:18:30 字数 419 浏览 1 评论 0原文

表 A

ID    EmpNo   Grade
--------------------    
1      100    HIGH
2      105    LOW
3      100    MEDIUM
4      100    LOW
5      105    LOW

查询：

select * 
from A 
where EMPNO = 100 
  and rownum <= 2 
order by ID desc

我尝试使用此查询来检索 max 和 max-1 值；我需要比较 max 和 max-1 的等级，如果等于，我需要将标志设置为“Y”或“N”而不使用光标。我也不想将整个记录扫描两次。

请帮我。

原文

Table A

ID    EmpNo   Grade
--------------------    
1      100    HIGH
2      105    LOW
3      100    MEDIUM
4      100    LOW
5      105    LOW

Query:

select * 
from A 
where EMPNO = 100 
  and rownum <= 2 
order by ID desc

I tried this query to retrieve max and max-1 value; I need to compare the grade from max and max-1, if equals I need to set the flag as 'Y' or 'N' without using a cursor. Also I don't want to scan the entire record twice.

Please help me.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

草莓味的萝莉 2024-11-14 21:18:30

ROWNUM 在 ORDER BY 之前应用，因此您需要像这样嵌套查询：

select * from
(select * from A where EMPNO =100 order by ID desc)
where rownum<=2

只执行一次表扫描（或者可能使用 EMPNO 上的索引）。

ROWNUM is applied before ORDER BY, so you need to nest the query like this:

select * from
(select * from A where EMPNO =100 order by ID desc)
where rownum<=2

That only performs one table scan (or it may use an index on EMPNO).

回复收藏 0 原文

爱殇璃 2024-11-14 21:18:30

select *
from (
select id, emp_no, grade
       , case 
          when lag(grade) over (order by emp_no desc) = grade 
          then 'Y' 
          else 'N'     
          end
          as flag
      , dense_rank() over( order by emp_no desc)  as rank
from t
)
where rank <=2
;

select *
from (
select id, emp_no, grade
       , case 
          when lag(grade) over (order by emp_no desc) = grade 
          then 'Y' 
          else 'N'     
          end
          as flag
      , dense_rank() over( order by emp_no desc)  as rank
from t
)
where rank <=2
;

回复收藏 0 原文

~没有更多了~