表名：document_archive_doc_perms
表说明：
- location char(1), document_id number(5), permission_id number(5),
permission_type char(1), date_time_from date, date_time_to date,
ecode number(5), ecode_s number(2), approved_by number(5),
approved_by_ecode number(2), approved_on date, primary
key(location,document_id,permission_id));
我的查询：
select permission_id,document_id,date_time_from,date_time_to,approved_on,permission_type from document_archive_doc_perms
where document_id=3 and ecode=1695 and approved_on is not null and (sysdate between date_time_from and date_time_to);
我的输出是
PERMISSION_ID DOCUMENT_ID DATE_TIME DATE_TIME APPROVED_ P
------------- ----------- --------- --------- --------- -
5 3 01-DEC-14 31-DEC-14 08-DEC-14 V
7 3 09-DEC-14 31-DEC-14 09-DEC-14 P
在这里，我需要的是记录中的最新权限（即，permit_id的最大值），该如何做？
您可以使用分析rank()调用：
SELECT permission_id,
document_id,
date_time_from,
date_time_to,
approved_on,
permission_type
FROM (SELECT permission_id,
document_id,
date_time_from,
date_time_to,
approved_on,
permission_type,
RANK() OVER (ORDER BY perission_id ASC) AS rk
FROM document_archive_doc_perms
WHERE document_id = 3 AND
ecode = 1695 AND
approved_on IS NOT NULL AND
SYSDATE BETWEEN date_time_from AND date_time_to
)
WHERE rk = 1
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句