我有一个带有父行的结果集。(儿童排从来没有孩子(。我需要对此进行分页(考虑到类型(,以便:
首先需要在分页页面上选择父母(例如,当页面大小= 10时,它必须包含< = 10个父行(,然后"坚持"在分页页中的那些父母的孩子。
源结果集看起来像:
+----+-----------+-------------+
| ID | PARENT_ID | SORT_COLUMN |
+----+-----------+-------------+
| 1 | | y |
| 2 | 1 | z |
| 3 | | u |
| 4 | | q |
| 5 | 4 | o |
| 6 | 4 | p |
| 7 | | c |
+----+-----------+-------------+
〜所需结果:
+----+-----------+-------------+----+----------+
| ID | PARENT_ID | SORT_COLUMN | RN | RN_CHILD |
+----+-----------+-------------+----+----------+
| 7 | | c | 1 | |
| 4 | | q | 2 | |
| 5 | 4 | o | 2 | 1 |
| 6 | 4 | p | 2 | 2 |
| 3 | | u | 3 | |
| 1 | | y | 4 | |
| 2 | 1 | z | 4 | 1 |
+----+-----------+-------------+----+----------+
现在我这样做:
with
cte as
(select 1 as id, null as parent_id, 'y' as sort_column from dual
union all
select 2 as id, 1 as parent_id, 'z' as sort_column from dual
union all
select 3 as id, null as parent_id, 'u' as sort_column from dual
union all
select 4 as id, null as parent_id, 'q' as sort_column from dual
union all
select 5 as id, 4 as parent_id, 'o' as sort_column from dual
union all
select 6 as id, 4 as parent_id, 'p' as sort_column from dual
union all
select 7 as id, null as parent_id, 'c' as sort_column from dual)
select
*
from
(select
t.*,
dense_rank() over (order by
case when t.parent_id is null
then
t.sort_column
else
(select t2.sort_column from cte t2 where t2.id = t.parent_id)
end) as RN,
case
when parent_id is null
then
null
else
row_number() over (partition by t.parent_id order by t.sort_column)
end as RN_CHILD
from cte t)
--where RN between :x and :y
order by RN, RN_CHILD nulls first
,但我认为这可以在无需额外访问结果集的情况下完成。(select t2.sort_column from cte t2 where t2.id = t.parent_id
(。
如何做?
upd:父母必须按sort_column进行排序,父母内的孩子也必须按sort_column进行排序。
首先有一个技巧,即值语句,其次,您可以使用左JOIN并凝聚来获得您想要的东西,例如:
with
cte(id,parent_id,sort_column) as
(
VALUES
(1, null, 'y' ),
(2, 1 , 'z' ),
(3, null, 'u' ),
(4, null, 'q' ),
(5, 4 , 'o' ),
(6, 4 , 'p' ),
(7, null, 'c' )
),
cte_branch as
(
SELECT coalesce(parent.id, cte.id) as branch_id, cte.id, cte.parent_id, cte.sort_column,
row_number over (partition by coalesce(parent.id, cte.id) order by cte.sort_column) as rn
FROM cte
left join cte parent on cte.parent_id = parent.id
)
select * from cte_branch
order by rn, id nulls first
您需要使用DENSE_RANK
作为RN,而ROW_NUMBER
对于CHILD_RN如下:
with
cte(id,parent_id,sort_column) as
(
SELECT 1, null, 'y' FROM DUAL UNION ALL
SELECT 2, 1 , 'z' FROM DUAL UNION ALL
SELECT 3, null, 'u' FROM DUAL UNION ALL
SELECT 4, null, 'q' FROM DUAL UNION ALL
SELECT 5, 4 , 'o' FROM DUAL UNION ALL
SELECT 6, 4 , 'p' FROM DUAL UNION ALL
SELECT 7, null, 'c' FROM DUAL
)
SELECT
C.*,
DENSE_RANK() OVER(
ORDER BY
(CASE
WHEN C.PARENT_ID IS NOT NULL THEN C.PARENT_ID
ELSE C.ID
END) DESC NULLS FIRST
) AS RN,
CASE
WHEN C.PARENT_ID IS NOT NULL THEN ROW_NUMBER() OVER(
PARTITION BY C.PARENT_ID
ORDER BY
C.ID
)
END AS CHILD_RN
FROM
CTE C
ORDER BY
RN;
输出:
ID PARENT_ID S RN CHILD_RN
---------- ---------- - ---------- ----------
7 c 1
4 q 2
5 4 o 2 1
6 4 p 2 2
3 u 3
2 1 z 4 1
1 y 4
7 rows selected.
欢呼!
在我的情况下,可以使用connect_by_root
子句,而不是额外访问结果集:
(我也注意到我的原始SQL包含一个错误 - 具有相同sort_column
值的父母具有相同的RN
值,此处已修复(
with
cte(id, parent_id, sort_column) as
(select 1, null, 'y' from dual union all
select 2, 1, 'z' from dual union all
select 3, null, 'u' from dual union all
select 4, null, 'q' from dual union all
select 5, 4, 'o' from dual union all
select 6, 4, 'p' from dual union all
select 7, null, 'c' from dual)
select
*
from
(select
t.*,
case when t.parent_id is null
then
row_number() over (partition by parent_id order by t.sort_column)
else
dense_rank() over (order by connect_by_root sort_column)
end as RN as RN,
case
when parent_id is null
then
null
else
row_number() over (partition by t.parent_id order by t.sort_column)
end as RN_CHILD
from cte t
connect by prior id = parent_id
start with parent_id is null)
--where RN between :x and :y
order by RN, RN_CHILD nulls first