Estoy usando la base de datos SQLite de Android y tengo el resultado a continuación:

 id | root_id | parent_id   |name
----------------------------------
613 | null    | null        | m1
612 | null    | null        | m4
570 | null    | null        | m1
635 | 570     | 570         | m6
653 | 570     | 635         | m1
652 | 570     | 635         | m3
632 | 570     | 570         | m9
392 | null    | null        | m2
753 | 392     | 392         | m5
751 | 392     | 392         | m4
391 | null    | null        | m7

Obtengo este resultado con la consulta a continuación:

WITH RECURSIVE all_employees(id, root_id, parent_id, name) AS (
    SELECT id, id AS root_id, parent_id, name FROM employees WHERE parent_id IS NULL
    UNION ALL
    SELECT c.id, (CASE WHEN c.parent_id IS NOT NULL THEN p.root_id END), c.parent_id, c.name FROM employees c JOIN all_employees p ON p.id = c.parent_id ORDER BY id DESC
)

SELECT id, (CASE WHEN id != root_id THEN root_id ELSE NULL END) root_id, parent_id, name FROM all_employees

Quiero establecer un límite para filas repetidas con el mismo root_id, por ejemplo: Cargando 2 filas con root_id 570 y 1 fila con root_id 392:

 id | root_id | parent_id   |name
----------------------------------
613 | null    | null        | m1
612 | null    | null        | m4
570 | null    | null        | m1
635 | 570     | 570         | m6
653 | 570     | 635         | m1
392 | null    | null        | m2
753 | 392     | 392         | m5
391 | null    | null        | m7
2
ali-star 12 jul. 2021 a las 09:53

3 respuestas

La mejor respuesta

Crea otro CTE:

counters(root_id, n) AS (VALUES (570, 2), (392, 1)) 

Donde devuelve todas las root_id s que desea restringir sus filas y el número de filas para cada una y luego usa una combinación LEFT de la CTE recursiva a eso.
Finalmente, establezca la condición en la cláusula WHERE con una subconsulta correlacionada:

WITH 
  RECURSIVE all_employees(id, root_id, parent_id, name) AS (
    SELECT id, id AS root_id, parent_id, name 
    FROM employees 
    WHERE parent_id IS NULL
    UNION ALL
    SELECT c.id, (CASE WHEN c.parent_id IS NOT NULL THEN p.root_id END), c.parent_id, c.name 
    FROM employees c JOIN all_employees p 
    ON p.id = c.parent_id 
  ),
  counters(root_id, n) AS (VALUES (570, 2), (392, 1))
SELECT a.id, (CASE WHEN a.id <> a.root_id THEN a.root_id END) root_id, a.parent_id, a.name
FROM all_employees a LEFT JOIN counters c
ON c.root_id = a.root_id
WHERE c.root_id IS NULL 
   OR (SELECT COUNT(*) FROM all_employees b WHERE b.root_id IS a.root_id AND b.id <= a.id) <= c.n

Tenga en cuenta que la cláusula ORDER BY dentro de CTE es inútil, porque no se garantiza que las filas se devuelvan en ese orden cuando seleccione CTE.
Puede establecer el orden de las filas que desee en la consulta final.

1
forpas 12 jul. 2021 a las 10:13

Unión de selecciones limitadas

WITH RECURSIVE all_employees(id, root_id, parent_id, name) AS (
    SELECT id, id AS root_id, parent_id, name 
    FROM employees 
    WHERE parent_id IS NULL
    UNION ALL
    SELECT c.id, (CASE WHEN c.parent_id IS NOT NULL THEN p.root_id END), c.parent_id, c.name 
    FROM employees c 
    JOIN all_employees p ON p.id = c.parent_id ORDER BY id DESC
)
SELECT id, root_id, parent_id, name
FROM all_employees
where root_id not in (570, 392)
union all
select *
from (
   select *
   from all_employees
   where root_id =570 
   limit 3
)
union all
select *
from (
   select *
   from all_employees
   where root_id =392
   limit 2
)
1
Serg 12 jul. 2021 a las 08:37

Agregue un contador al CTE y luego use la lógica WHERE para el filtrado:

WITH RECURSIVE all_employees(id, root_id, parent_id, name, lev) AS (
    SELECT id, id AS root_id, parent_id, name, 0 as lev
    FROM employees
    WHERE parent_id IS NULL
    UNION ALL
    SELECT c.id,
           (CASE WHEN c.parent_id IS NOT NULL THEN p.root_id END), c.parent_id, c.name, p.lev + 1
    FROM employees c JOIN
         all_employees p
         ON p.id = c.parent_id
    ORDER BY id DESC   -- don't think this does anything
   )
SELECT id, (CASE WHEN id <> root_id THEN root_id END) as root_id, parent_id, name
FROM all_employees
WHERE root_id = 570 AND cnt <= 1 OR
      root_id = 392 AND cnt <= 2 OR
      root_id NOT IN (570, 392);

El punto aquí es poner el recuento en el CTE recursivo y luego usar esa información para filtrar. Puede formular la cláusula WHERE como prefiera:

WHERE (CASE WHEN root_id = 570 THEN cnt <= 1
            WHEN root_id = 392 THEN cnt <= 2
            ELSE 1
       END)
            
0
Gordon Linoff 12 jul. 2021 a las 10:48