本文介绍了获取结果集的最后一条记录的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
以下是我用来获取select查询返回结果的最后一条记录的sql查询(oracle).这是最佳方法吗?
Following is the sql query (oracle) that I use to get the last record of the returning result of the select query. Is this the optimum way?
SELECT HAZMAT_PLACARD_NOTATION
INTO v_pcn
FROM HAZMAT_CLASS_IRF
WHERE HAZMAT_CD = p_stcc_cd and ROWID = (SELECT MAX(ROWID) FROM HAZMAT_CLASS_IRF WHERE HAZMAT_CD = p_stcc_cd);
以下是选择查询的示例结果集,其中HAZMAT_CD = 4920111但是,应该检索具有HAZMAT_PLAYCARD_NOTATION值自发可燃的最后一行.这就是目标
Following is an example result set of the select query where HAZMAT_CD = 4920111But the last row with the HAZMAT_PLAYCARD_NOTATION value SPONTANEOUSLY COMBUSTIBLE should be retrieved. That's the objective
推荐答案
有很多方法:
Oracle Pre-12c
版本:
- 子查询中的
- ROWNUM ,外部查询中的 ORDER BY
- 分析功能
- ROWNUM in sub-query and ORDER BY in outer query
- Analytic function
Oracle 12c
版本:
- 前n行限制功能
使用 ROWNUM
SELECT HAZMAT_PLACARD_NOTATION
INTO v_pcn
FROM
(SELECT HAZMAT_PLACARD_NOTATION,
ROWNUM rn
FROM HAZMAT_CLASS_IRF
WHERE HAZMAT_CD = p_stcc_cd
ORDER BY <sort_column> DESC
)
WHERE rn =1;
使用前n行限制功能
SELECT HAZMAT_PLACARD_NOTATION
INTO v_pcn
FROM HAZMAT_CLASS_IRF
WHERE HAZMAT_CD = p_stcc_cd
ORDER BY <sort_column> DESC
FETCH FIRST 1 ROW ONLY;
请查看此答案以获取示例和详细说明.
Have a look at this answer for examples and detailed explanation.
这篇关于获取结果集的最后一条记录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!