ORACLE 查询id在pid下面的排序
程序员文章站
2022-06-05 21:02:34
...
有一个树形结构id,pid,name现有需求需要查询id在pid下的排列顺序
不了解oracle函数之前最开始的写法是这样的:
SELECT rownum AS rn, a.id
FROM (
SELECT *
FROM SE_REQ_ITEM_INFO
WHERE PARENT_ID = (
SELECT PARENT_ID
FROM SE_REQ_ITEM_INFO
WHERE ID = 75
)
AND DOCUMENT_ID = 5251
ORDER BY ranking ASC
) a
经过学习发现oracle的窗口函数真的很好用
SELECT id, row_number() over(partition by PARENT_ID order by ranking asc)
FROM
SE_REQ_ITEM_INFO
where DOCUMENT_ID=5251 and id in (0,74,75)
推荐阅读