У меня есть id столбца, parent столбец и path столбца, который является материализованным путем.
Это выглядит как
1 | \N | 1 2 | 1 | 1/2 3 | 2 | 1/2/3 4 | 3 | 1/2/3/4 5 | 3 | 1/2/3/5 6 | 2 | 1/2/6 7 | 6 | 1/2/6/7 8 | 2 | 1/2/8 9 | 1 | 1/9 10 | 9 | 1/9/10 11 | 10 | 1/9/10/11 12 | 11 | 1/9/10/11/12 13 | 11 | 1/9/10/11/13 14 | 11 | 1/9/10/11/14 15 | 14 | 1/9/10/11/14/15 16 | 14 | 1/9/10/11/14/16 17 | 14 | 1/9/10/11/14/17 18 | 10 | 1/9/10/18 19 | \N | 19 20 | 19 | 19\20 21 | 19 | 19\21
Мне нужно сделать некоторые запросы, основанные на этой таблице.
Запросы, которые мне нужно сделать, это
id 9 SELECT * FROM `tester` WHERE 'path' LIKE '%/9/%';
Будет работать нормально, пока вы не замените идентификатор 1 или 19, поскольку в начале нет.
SELECT * FROM `tester` WHERE 'path' LIKE '%1/%';
будет выбирать все строки, где число заканчивается на 1, так что 1, 11, 21, 31, 211 и т. д.
SELECT * FROM `tester` WHERE 'path' LIKE '1/%';
будет работать правильно для строк 1 или 19
Таким образом, SELECT * FROM tester WHERE 'path' LIKE '1/%' OR 'path' LIKE '%/1/%' ;
Является ли лучшее, что я могу придумать, любые предложения?
Select * from тестера, where 'parent' = 9 ; будет работать нормально.
Поэтому я хочу получить либо одну строку строк level1, level2, level3, ... levelx или x, представляющих разные уровни,
Притворим, что x для этого примера будет 3. Строки из этого примера будут 9, 8, 6 (4-й уровень, если мы попросили, будет 3)
Есть идеи?
редактировать
#select count of children of specific node(5) down to a maximum of three levels, do no include the parent SELECT COUNT(child.id) children, LENGTH(REPLACE(child.path, parent.path, '')) - LENGTH(REPLACE(REPLACE(child.path, parent.path, ''), '/', '')) AS LEVEL FROM `tester` child JOIN `tester` parent ON child.path LIKE CONCAT(parent.path,'%') WHERE parent.id =5 GROUP BY LEVEL HAVING LEVEL <= 3 AND LEVEL > 0;
Итак, снова для этого примера мы будем использовать 3 как x.
Мы хотим вернуться
10 | 1 11 | 2 18 | 2 12 | 3 13 | 3 14 | 3
Снова я полностью потеряю, как это сделать.
Редактировать:
#select all information, and relative level from parent of children of specific node(5) down to a maximum of three levels, do no include the parent SELECT child.*, LENGTH(REPLACE(child.path, parent.path, '')) - LENGTH(REPLACE(REPLACE(child.path, parent.path, ''), '/', '')) AS LEVEL FROM `tester` child JOIN `tester` parent ON child.path LIKE CONCAT(parent.path,'%') WHERE parent.id =9 GROUP BY id HAVING LEVEL <= 3 AND LEVEL > 0;
Для того, чтобы дать вам голову, эти решения основаны на сравнении строк, не оптимизированы и не могут использовать индексы. вы должны по-разному нормализовать свои таблицы. (См. Управление иерархическими данными в MySQL )
По некоторым вопросам:
Выберите всех детей с идентификатором 9:
Поскольку столбец « Path не содержит ведущие и конечные косые черты, вам необходимо объединить их в путь:
SELECT * FROM tester WHERE CONCAT('/', path, '/') LIKE '%/9/%';
выберите совокупное количество 9 детей, x уровней:
Нам нужно группировать по числу косых черт в пути, минус количество косых черт в родительском пути:
SELECT (LENGTH(c.Path) - LENGTH(REPLACE(c.Path, '/', ''))) - (LENGTH(p.Path) - LENGTH(REPLACE(p.Path, '/', ''))) AS Level, COUNT(*) FROM tester c JOIN tester p ON c.Parent = p.ID WHERE CONCAT('/', path, '/') LIKE '%/9/%'; GROUP BY 1
Для простоты я использовал вышеприведенный запрос, чтобы показать все уровни. Если вы хотите ограничить глубину x, используйте предикат WHERE из запроса ниже.
выберите идентификатор детей от 9 до уровня x, уровень которого равен 9:
Мы исследуем столбец Path до количества томов, используя уровень родителей:
SELECT c.* FROM tester c JOIN tester p ON c.Parent = p.ID WHERE CONCAT( '/', SUBSTRING_INDEX( Path, '/', (LENGTH(p.Path) - LENGTH(REPLACE(p.Path, '/', ''))) + 4 ), '/') LIKE '%/9/%'
Мы предпринимаем следующие шаги:
LENGTH(p.Path) - LENGTH(REPLACE(p.Path, '/', '')) ) SUBSTRING_INDEX ).