MySQL проверяет родителей по детям
Я программирую в Visual Studio, но мне нужно выполнить проверку таблицы mySQL (v8.0.28), и я думаю, было бы быстрее, если бы она выполнялась как процедура на сервере. К сожалению, мои навыки программирования MySQL очень ограничены.
Это эквивалент моей таблицы:
Он работает как узлы Treeview, и я могу проверить состояние узлов нижнего уровня с помощью этой строки:
UPDATE `bs`.`valitable`
SET `NodeValid`='1'
WHERE `NodeLevel`='3' AND `NodeValue` > '2023-02-22';
Как я могу пройтись по узлам, где NodeLevel='2', потому что мне нужно проверить, все ли дочерние узлы действительны = 1 (тогда родитель действителен = 1), в противном случае родитель действителен = 0.
Я нашел несколько циклов WHILE...DO, FOR, Cursors, но я не могу написать правильный синтаксис для правильного выполнения этого действия. Или - есть ли другой способ, как это сделать?
Я пытался сделать это в Visual Studio, но это слишком медленно, потому что требуется загрузить всю таблицу, продолжить и сохранить обратно. Поскольку таблица растет, ее нельзя использовать, поэтому я надеюсь, что процедура mySQL может ускорить процесс, поскольку она может запускаться непосредственно с сервера.
Это слишком сложно для меня, спасибо за совет, я сделаю это в VS...
Это просто. Советы по правильному заданию вопроса на языке структурированных запросов (SQL), № 5 и № 3 — и я покажу вам, что это действительно просто.
OK @Akina, ожидаемый результат — редактирование столбца NodeValid на основе дочернего состояния NodeValid, как указано в вопросе. В моем случае это сложнее, чем 0/1, но оно основано на очень простых правилах, которые я могу изменить позже. У каждого слоя детей свои правила, поэтому я бы прошёл каждый уровень отдельно. Моя проблема в том, что я не могу просмотреть результаты "SELECT". Программа должна работать так (извиняюсь за отсутствие синтаксисов mySQL): NodeLevel 3 решается рассматриваемым кодом: UPDATE table SET NodeValid=1 WHERE NodeLevel=3 AND NodeValue > 2023-02-22;
NodeLevel 2, 1 и 0 должны выполняться следующим образом: Получить всех родителей NodeLevel=2 ArrayX = SELECT DISTINCT(Nodeparent) FROM table WHERE NodeLevel=2; Для каждого ArrayX проверить все дочерние NodeValid If any ( SELECT NodeValid FROM table WHERE NodeParent= ArrayX ; ) = 0 then ArrayX NodeValid=0 Надеюсь, я достаточно ясен...
Я получаю одобрение от своих детей. Но только когда я их кормлю.
Если вам нравится делать это вручную и получать почасовую оплату, сделайте это, но если вы предпочитаете щелкнуть правой кнопкой мыши и покончить с этим, используйте Workbench и никогда не оглядывайтесь назад --> dev.mysql.com/downloads/ верстак
Ответы 1
Вы должны изменить свою таблицу так, чтобы вы представляли свои узлы NodeLevel 0 с помощью NodeParent NULL вместо root. В любом случае это требуется для ограничения FK от NodeParent до NodeID, которое у вас должно быть на месте.
Этот подход использует рекурсивный cte для построения полного списка конечных узлов (NodeLevel = 3) и всех их предков. Итак, основываясь на вашем описании, мы можем обновить всех предков до допустимых, если все связанные конечные узлы действительны (NodeValue > '2023-02-22'):
WITH RECURSIVE cte (AncestorID, LeafID) AS (
SELECT NodeParent, NodeID
FROM valitable WHERE NodeLevel = 3
UNION ALL
SELECT v.NodeParent, c.LeafID
FROM valitable v
JOIN cte c ON v.NodeID = c.AncestorID AND v.NodeParent IS NOT NULL
)
UPDATE valitable a
JOIN (
SELECT cte.AncestorID, MIN(l.NodeValue > '2023-02-22') valid
FROM cte
JOIN valitable l ON cte.LeafID = l.NodeID
GROUP BY cte.AncestorID
) j ON a.NodeID = j.AncestorID
SET a.NodeValid = j.valid;
Чтобы это работало, вам нужно работать так, как ожидалось, вам нужно сначала запустить начальное обновление узлов NodeLevel 3.
Это не внесет никаких изменений в узлы, которые не имеют отношения к узлу NodeLevel 3, так как нет объяснения правил для этих узлов.
Обновлять
Чтобы установить NodeValid = NULL, где узел не имеет отношения к узлу NodeLevel 3, вы можете изменить JOIN на LEFT JOIN в операторе обновления, но вам нужно убедиться, что он обновляется только там, где NodeLevel < 3, иначе будут NULL все узлы NodeLevel 3. , так как они не включены как предки в cte:
-- start with your initial update
UPDATE valitable
SET NodeValid = NodeValue > '2023-02-22' -- 1 if meets criterion, 0 if not
WHERE NodeLevel = 3;
WITH RECURSIVE cte (AncestorID, LeafID) AS (
SELECT NodeParent, NodeID
FROM valitable WHERE NodeLevel = 3
UNION ALL
SELECT v.NodeParent, c.LeafID
FROM valitable v
JOIN cte c ON v.NodeID = c.AncestorID AND v.NodeParent IS NOT NULL
)
UPDATE valitable a
LEFT JOIN (
SELECT cte.AncestorID, MIN(l.NodeValue > '2023-02-22') valid
FROM cte
JOIN valitable l ON cte.LeafID = l.NodeID
GROUP BY cte.AncestorID
) j ON a.NodeID = j.AncestorID
SET a.NodeValid = j.valid
WHERE a.NodeLevel < 3;
Спасибо за Ваш ответ! Можно ли изменить значение на NULL для всех родительских узлов без привязки к NodeLevel 3? Где 0 доминирует над NULL?
Ответ обновлен. Я изменил ваше первоначальное обновление узлов NodeLevel 3, чтобы оно аннулировало узлы, которые не соответствуют критерию.
Вам нужно в рекурсивном CTE - изучите предложение WITH (Common Table Expressions).