Рекурсивдик CTE жана иерархиялык маалымат

Дарактарга, уюм структурасына жана жолдорго өзүнө шилтеме кылган суроо аркылуу кайрылуу

Негизги идея: рекурсивдик жалпы таблицалык туюнтма (CTE) — бул өзүнө шилтеме кыла алган аталышы бар суроо, ошондуктан бир гана SQL оператору белгисиз тереңдиктеги иерархияны кыдырып чыга алат. Сиз анкер бөлүгүн (баштапкы саптар) жана рекурсивдик бөлүктү (мурунку кадамдан алынган саптар) жазып, аларды UNION ALL менен бириктиресиз, ал эми кыймылдаткыч рекурсивдик бөлүк бир да сап кайтарбаганча итерацияларды улантат. Дал ушундай жол менен уюм структурасын жайып, категория дарагын ачып же түйүндөн тамырга чейинки жолду канча деңгээл бар экенин билбестен издешет.

Сабактын аягында сиз төмөнкүлөрдү жасай аласыз:

WITH RECURSIVE анатомиясын окуй аласыз — UNION ALL менен туташтырылган анкер жана рекурсивдик бөлүктөр,
ата-эне/бала таблицасын (уюм структурасын, категория дарагын) жогорудан төмөн жана төмөндөн жогору кыдыра аласыз,
төмөн түшкөндө тереңдик деңгээлин көзөмөлдөп, жол сабын түзө аласыз,
рекурсияны токтотуу шарты менен чектеп, MySQLдеги тереңдик лимитин түшүнөсүз,
циклдерди байкап, ар бир диалект кандай коргоо берерин билесиз.

Мурунку курстардан кадимки (рекурсивдик эмес) CTE, JOIN жана подсуроолорду өздөштүргөн деп болжолдонот. Бул жерде биз болгону өзүнө шилтемени кошобуз.

1. Мисал база

Төмөндөгү ар бир суроо бир кичине компаниянын базасына каршы иштейт. employee таблицасы өзүнө шилтеме кылат: ар бир сап ошол кызматкердин жетекчисинин id-син сактайт, ал эми башкы директордо ал NULL. Бул бир гана manager_id тилкеси каалагандай тереңдиктеги бүтүндөй уюм структурасын коддойт.

CREATE TABLE employee (
    id          INT PRIMARY KEY,
    name        VARCHAR(50) NOT NULL,
    manager_id  INT NULL,
    salary      INT NOT NULL,
    FOREIGN KEY (manager_id) REFERENCES employee(id)
);

INSERT INTO employee (id, name, manager_id, salary) VALUES
    (1, 'Alice', NULL, 300000),
    (2, 'Bob',   1,    200000),
    (3, 'Carol', 1,    190000),
    (4, 'Dan',   2,    120000),
    (5, 'Eve',   2,    125000),
    (6, 'Frank', 3,    110000),
    (7, 'Grace', 4,    90000),
    (8, 'Heidi', 4,    92000);

Сүрөттөп берсек, баш ийүү сызыктары мындай көрүнөт:

Alice (CEO)
  - Bob
      - Dan
          - Grace
          - Heidi
      - Eve
  - Carol
      - Frank

Ошондой эле биз товар каталогунун дарагы үчүн экинчи өзүнө шилтеме кылган category таблицасын колдонобуз. Анын формасы ошондой эле: ошол эле таблицага кайра көрсөткөн parent_id тилкеси.

CREATE TABLE category (
    id        INT PRIMARY KEY,
    name      VARCHAR(50) NOT NULL,
    parent_id INT NULL
);

INSERT INTO category (id, name, parent_id) VALUES
    (1, 'Electronics', NULL),
    (2, 'Computers',   1),
    (3, 'Laptops',     2),
    (4, 'Desktops',    2),
    (5, 'Phones',      1),
    (6, 'Audio',       1),
    (7, 'Headphones',  6);

Рекурсивдик CTE жана иерархиялык маалымат

Рекурсивдик CTE жана иерархиялык маалымат

1. Мисал база

Бул сабакты YourAIZone платформасында улантыңыз

Толук сабакта