MySQL raarrange результаты запроса
Это моя таблица с примерами данных:
| attr_key | attr_value | element_id |<br>
| color | black | 1 |<br>
| color | red | 2 |<br>
| color | green | 3 |<br>
| power | 82 | 1 |<br>
| power | 150 | 2 |<br>
| power | 240 | 3 |<br>
| and so on ...
Как я могу запросить эту таблицу, чтобы получить такой результат:
| color | power | element_id |<br>
| black | 82 | 1 |<br>
| red | 150 | 2 |<br>
| green | 240 | 3 |
GROUP BY не работает, потому что он принимает только первый атрибут для каждого element_id, а остальные теряются.
Мне нужно создать представление?
Было бы лучше поместить атрибуты прямо в мою таблицу elements, например в JSON-поле? Эти атрибуты очень изменчивы, поэтому для каждого элемента может быть много атрибутов (более 20). Я не уверен, какое решение будет работать лучше, когда мне нужно запросить и проверить определенные атрибуты (все или только некоторые). .
Позже я хочу ПРИСОЕДИНЯТЬСЯ к другой таблице (elements), используя element_id.
Вам следует изменить дизайн своей таблицы, чтобы она всегда сохранялась как второй рисунок. На самом деле нет необходимости хранить данные, поскольку они выложены в первой таблице.
@Bleach, ты серьезно? Это будет означать, что новый столбец нужно будет добавлять каждый раз, когда кто-то создает новый ключ атрибута.
Это преобразование называется поворотным, и здесь, на ТАК, много раз спрашивали и отвечали на него. Связанная тема-дубликат демонстрирует, как выполнить это преобразование в mysq. Тем не менее, пожалуйста, обратите внимание, что это может быть более эффективно выполнить в коде приложения, чем в mysql.
@Shadow поправьте меня, если я ошибаюсь, но сводные таблицы используются для разделения отношений n-to-m-. В моем случае это отношение 1-n. Каждый элемент имеет определенное количество атрибутов, но каждый атрибут принадлежит только одному элементу.
Я поправляю вас :) Помимо пары ORM, таблицы соединений или ассоциаций никто не называет сводными (см. en.m.wikipedia.org/wiki/Associative_entity). Вы также можете проверить описание тега сводной таблицы.
Ответы 1
Это сделает то, что вам нужно (замените все экземпляры test_table на свое имя таблицы):
SELECT
sub1.`color` AS `Color`,
sub2.`power` AS `Power`,
sub1.`element_id` AS `Element ID`
FROM
(SELECT element_id, attr_value AS `color` FROM test_table WHERE attr_key = 'color') AS sub1
INNER JOIN
(SELECT element_id, attr_value AS `power` FROM test_table WHERE attr_key = 'power') AS sub2
ON
sub1.element_id = sub2.element_id
Код, который я использовал для настройки теста:
DROP TABLE IF EXISTS test_table;
CREATE TABLE IF NOT EXISTS test_table(
element_id int,
attr_key varchar(40),
attr_value varchar(40)
);
INSERT INTO
test_table
(attr_key, attr_value, element_id)
VALUES
('color', 'black', 1),
('color', 'red', 2),
('color', 'green', 3),
('power', '82', 1),
('power', '150', 2),
('power', '240', 3);
Просто примечание: group by не принимает первый элемент. Он группирует результаты, и вы должны использовать агрегаты и т. д., Чтобы определить, что вы хотите от сгруппированных данных. MySQL допускает глупые вещи с группировкой по и возвращает случайные результаты, если это не сделано должным образом, но при этом изучает реальную функциональность и не предполагает вещей, основанных на том, какие результаты вы случайно видите.