主页 / 问题 / 1003214
Accepted
Alex
Asked:2020-07-16 09:20:00 +0000 UTC2020-07-16 09:20:00 +0000 UTC

如何为树中的所有父项检查表中字段的状态

  • 772

源表 - 类别树

id, parent_id, state, name

我们有

id = 4, parent_id = 0, state = 1, name = Подкатегория 0 (корневая)
id = 8, parent_id = 4, state = 0, name = Подкатегория 1
id = 15, parent_id = 8, state = 1, name = Подкатегория 2
id = 36, parent_id = 15, state = 1, name = Подкатегория 3

如何用一个请求检查所有父母的状态，如果至少有一个状态= 0，那么选择的结果应该是0行

尝试使用递归

WITH RECURSIVE cte (id, state, parent_id, name) AS (
      SELECT     id,
                 state,
                 parent_id,
                 name
      FROM       t_pages
      WHERE      id = 36
      UNION ALL
      SELECT     p.id,
                 p.state,
                 p.parent_id,
                 p.name
      FROM       t_pages p
      INNER JOIN cte
                 ON p.id = cte.parent_id
                 AND p.state = 1
    )
    SELECT * FROM cte;

但我不知道如何正确设置条件。在上面的变体中，它总是给出 1 个变体（这是合乎逻辑的）好吧，它工作得太慢了 - 每个 200 条记录的表 0.5 秒

还有其他选择吗？

UPD：小提琴https://dbfiddle.uk/?rdbms=mariadb_10.4&fiddle=ecdbd0252a87aed8d3c6f5b3a31e0297

mysql

1 个回答

  1. Best Answer
    SET @num:=6;

WITH RECURSIVE
cte AS ( SELECT id, parent_id, state FROM t_pages p WHERE p.id = @num
         UNION ALL
         SELECT p.id, p.parent_id, p.state FROM t_pages p JOIN cte ON p.id = cte.parent_id )
SELECT *
FROM t_pages 
WHERE id = @num
  AND ( SELECT MIN(state) FROM cte ) > 0

    小提琴

    • 1

相关问题