#mysql #sql #group-by #where-clause
#mysql — сервер #sql #группировать по #where-предложение #mysql
Вопрос:
У меня есть следующая таблица:
CREATE TABLE poke_evolutions (
id integer PRIMARY KEY,
poke_id VARCHAR(20),
parent VARCHAR(20,
method VARCHAR(20)
)
И этот образец данных:
INSERT INTO poke_evolutions (id,poke_id,parent,method)
VALUES (1,'Pichu',null,'Happiness')
INSERT INTO poke_evolutions (id,poke_id,parent,method)
VALUES (2,'Pikachu','Pichu','Thunderstone')
INSERT INTO poke_evolutions (id,poke_id,parent,method)
VALUES (3,'Raichu','Pikachu','Thunderstone')
INSERT INTO poke_evolutions (id,poke_id,parent,method)
VALUES (4,'Raichu Alola','Pikachu','Thunderstone')
Мне нужен запрос, который, учитывая ‘poke_id’ или ‘parent’, возвращает полное семейство эволюций. Например:
Given "Pikachu" it must retrieve ids: 1,2,3,4
Given "Raichu Alola" it must retrieve ids: 1,2,3,4
РЕДАКТИРОВАТЬ: объяснение логики этого:
Покемон может эволюционировать , у покемона могут быть эволюции, предэволюции и расщепленные эволюции :
Пример нормальной эволюции:
Комментарии:
1. Пожалуйста, объясните логику. Это не очевидно, если вы не фанат покемонов.
2. Выберите ОДНУ СУБД, затем удалите несвязанный тег.
3. Дизайн данных от вас или вы перекачали его в другое место? Потому что, честно говоря, мне это кажется плохим. Это неправильный способ хранения иерархических данных и управления ими. Это может работать со сложными запросами, но в какой-то момент у вас возникнет много проблем. Например, у вас должна быть таблица символов со столбцами
ID
number,Name
(Pichu, Pikachu, Raichu),Instance
NULL, 1, 2 (для ваших 2 экземпляров Raichu). И затем в вашей таблице evo столбцами должны быть ID, character_id, parent_char_id, description_of_evo. Слишком длинный комментарий, я надеюсь, вы его поняли.4. Я говорю вам, что символы и эволюции — это нечто другое, и они должны быть разделены на 2 таблицы. Один из них должен обрабатывать иерархию с 2 столбцами «Character_ID» и «Parent_Character_id», он может работать в обеих таблицах. Самый первый родитель (Пичу, Бульбасавр) в качестве parent_id=NULL. Это единственный эффективный способ управлять данными о найме и запрашивать их в SQL. Но ваша главная проблема прямо сейчас заключается в том, что ваша таблица evo хранит символ в 3 столбцах: poke_id, evo, split_evo. Это явно неправильно и рано или поздно приведет вас к множеству проблем. Исправьте это в первую очередь
5. Если кто-то придет сюда с запросом, который решит вашу проблему, это только замаскирует реальность: ваше моделирование данных неверно. И позже возникнет множество проблем. Я мог бы, но я не дам вам ответа, потому что это НЕ делает вам одолжения. Напротив, если вы измените свою структуру данных и то, что я сказал, вы легко найдете массу примеров иерархических запросов, которые помогут вам достичь того, чего вы хотите, и за его пределами.
Ответ №1:
Я предполагаю , что вы используете MySQL версии 8 . Если нет, то это серьезная проблема, потому что наличие возможности запускать рекурсивный CTE — роскошь с иерархическими данными. Все, что приведено ниже, зависит от CTE. Это все еще возможно сделать без CTE, в MySQL 5, но я не буду подробно описывать это здесь, потому что у этого нет будущего.
Прежде всего, добавьте столбец, в котором будет храниться символ «происхождения» определенной «цепочки» эволюции. Почему ? Потому что вы сталкиваетесь с типичной ситуацией, когда денормализация — это очень хорошая вещь.
Если вы не добавите этот столбец, вам придется выполнять чрезмерно сложные и неэффективные запросы повсюду, чтобы получить всех покемонов в цепочке определенного покемона.
Итак, начните с
ALTER table poke_evolutions ADD origin (VARCHAR(20)) default NULL;
И тогда ваш стол становится
id poke_id parent method origin
1 Pichu Happiness
2 Pikachu Pichu Thunderstone
3 Raichu Pikachu Thunderstone
4 Raichu Alola Pikachu Thunderstone
5 Bulbasaur Happiness
6 Ivysaur Bulbasaur Anything
7 Venusaur Ivysaur Anything
Но что замечательно, так это то, что вы можете определить один раз для всех origin
значение, используя рекурсивный CTE, который ищет самого верхнего родителя всех ваших покемонов :
WITH recursive cte AS(
SELECT *, poke_id AS topparent
FROM poke_evolutions
WHERE parent IS NULL
UNION ALL
SELECT p.*, c.topparent
FROM poke_evolutions p JOIN cte c ON c.poke_id = p.parent
WHERE p.poke_id <> p.parent
)
select * from cte
id poke_id parent method origin topparent
1 Pichu Happiness Pichu
5 Bulbasaur Happiness Bulbasaur
2 Pikachu Pichu Thunderstone Pichu
6 Ivysaur Bulbasaur Anything Bulbasaur
3 Raichu Pikachu Thunderstone Pichu
4 Raichu Alola Pikachu Thunderstone Pichu
7 Venusaur Ivysaur Anything Bulbasaur
Поэтому просто запустите запрос НА ОБНОВЛЕНИЕ, который использует ваш CTE, чтобы окончательно исправить исходное значение :
WITH recursive cte AS(
SELECT *, poke_id AS topparent
FROM poke_evolutions
WHERE parent IS NULL
UNION ALL
SELECT p.*, c.topparent
FROM poke_evolutions p JOIN cte c ON c.poke_id = p.parent
WHERE p.poke_id <> p.parent
)
UPDATE poke_evolutions p, cte c
SET p.origin= c.topparent
WHERE p.poke_id=c.poke_id;
Ваша таблица становится :
SELECT * from poke_evolutions;
id poke_id parent method origin
1 Pichu Happiness Pichu
2 Pikachu Pichu Thunderstone Pichu
3 Raichu Pikachu Thunderstone Pichu
4 Raichu Alola Pikachu Thunderstone Pichu
5 Bulbasaur Happiness Bulbasaur
6 Ivysaur Bulbasaur Anything Bulbasaur
7 Venusaur Ivysaur Anything Bulbasaur
И теперь вы можете легко делать то, что хотели в первую очередь
Учитывая «Pikachu», он должен получить идентификаторы: 1,2,3,4
Учитывая «Raichu Alola», он должен получить идентификаторы: 1,2,3,4
С Пичу
select poke_id from poke_evolutions
where origin = (select origin from poke_evolutions WHERE poke_id='Pichu');
poke_id
Pichu
Pikachu
Raichu
Raichu Alola
С Raichu Alola
select poke_id from poke_evolutions
where origin = (select origin from poke_evolutions WHERE poke_id='Raichu Alola');
poke_id
Pichu
Pikachu
Raichu
Raichu Alola
С Ивизавром
select poke_id from poke_evolutions
where origin = (select origin from poke_evolutions WHERE poke_id='Ivysaur');
poke_id
Bulbasaur
Ivysaur
Venusaur
И, наконец, добавьте индекс в свой origin
столбец, это ваш новый друг 😉
Комментарии:
1. Ваш ответ — чистое золото , спасибо за сегодняшний урок по оптимизации базы данных !
2. @NexussimLements добро пожаловать. Поэтому я предполагаю, что это было для MySQL, а не для sqlite. Если это так, отредактируйте свой вопрос и пометьте его должным образом. Мастер Гордон удалил тег hte MySQL