Версия для печати
Нажмите сюда для просмотра этой темы в оригинальном формате
Форум на Исходниках.RU > Базы данных: SQL > Объединение данных из нескольких таблиц в одной.


Автор: RedLight 18.10.23, 17:03
Есть таблица с множеством пользователей (в примере с одним).
<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    CREATE TABLE `users`(`id` INT, `name` TEXT);
    INSERT `users`(`id`, `name`) VALUES (1, 'Vasily Pupkin');

Существует неизвестное количество таблиц (в примере две) содержащих данные о пользователе.
<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    CREATE TABLE `values_1`(`user_id` INT, `value_1` INT);
    INSERT `values_1`(`user_id`, `value_1`) VALUES (1, 1);
     
    CREATE TABLE `values_2`(`user_id` INT, `value_2` INT);
    INSERT `values_2`(`user_id`, `value_2`) VALUES (1, 2);

Так можно получить фиксированное количество данных о пользователе.
<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    SELECT `users`.`name`, `values_1`.`value_1`, `values_2`.`value_2` FROM `users` INNER JOIN `values_1`, `values_2`;

Но, поскольку формат таблиц с данными одинаковый а их количество заранее не известно, то хотелось бы объединить таблицы с данными в одну, при этом добавив индекс данных (вроде как категория данных из соответствующей индексу таблицы).
<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    CREATE TABLE `values`(`user_id` INT, `value` INT, `index` INT);
    INSERT `values`(`user_id`, `value`, `index`) VALUES (1, 1, 1), (1, 2, 2);

Как составить запрос получения данных о пользователе аналогичный предыдущему, но работающий с объединённой таблицей?

Я понимаю, что все данные можно затолкать вообще в одну таблицу, где будет имя пользователя и все его данные, но тогда у неё будет фиксированное число столбцов, а мне бы хотелось заводить новые категории данных (с новыми индексами) и при этом не трогать структуру таблиц.

Автор: Majestio 19.10.23, 05:55
Цитата RedLight @
но тогда у неё будет фиксированное число столбцов

Немного неверное проектирование. Лучше, и гораздо проще, поступить вот как, с учетом:

Цитата RedLight @
поскольку формат таблиц с данными одинаковый

Создаем таблицы:

Users - таблица пользователей с полями Id и UserName
Categories - таблица категорий данных с полями Id и CategoryName
User2Categorу - таблица данных по категориям с полями UserId, CategoryId, Data

Все. Теперь можно динамически добавлять категории данных, и можно пользователям "привязывать" данные по категориям.
Другой вопрос - если захочется привязывать данные не одного типа (как сейчас по условию), а разных. Но это уже другой вопрос, требующий дальнейшей доработки. Там возможны варианты.

Автор: RedLight 19.10.23, 06:44
Здесь `Data` из `User2Categorу` заменён на `SkillLevel`.
<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    CREATE TABLE `Users`(`Id` INT, `UserName` TEXT);
    INSERT `Users`(`Id`, `UserName`) VALUES (1, 'Vasily Pupkin');
     
    CREATE TABLE `Categories`(`Id` INT, `CategoryName` TEXT);
    INSERT `Categories`(`Id`, `CategoryName`) VALUES (1, 'PHP Developers'), (2, 'English Speakers');
     
    CREATE TABLE `User2Categorу`(`UserId` INT, `CategoryId` INT, `SkillLevel` INT);
    INSERT `User2Categorу`(`UserId`, `CategoryId`, `SkillLevel`) VALUES (1, 1, 4), (1, 2, 3);

Правильно ли я понял предложенный пример?
Текстовые названия категорий в моём случае не нужны, хорошо бы их избежать.
Предложенная `User2Categorу` выглядит как моя таблица `values` и так же не ясно как сделать запрос на выборку пользователя с его данными из неё.
Имя пользователя, в принципе, тоже можно считать текстовыми данными, что бы рассмотреть пример с данными разных типов, так как ещё логин можно добавить, но пока не надо :) Таблица пользователей служебную информацию будет содержать, которая к данным не относится и всегда известны её количество и формат.

Автор: Akina 19.10.23, 07:10
Вы задаёте сразу несколько вопросов.

Первый - как правильно хранить. На этот вопрос уже ответил Majestio - мы имеем типичную M:N связь между пользователями и и данными, и храним в формате, аналогичном EAV.

Второй - как получить такие данные, если заранее не известно количество данных для одного пользователя. Тут ответ зависит от требуемого формата результата.

Если он должен быть как показано в первом посте, то решение простое - используем UNION ALL и литералы для фиксации того, из какой таблицы выбраны данные.
<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    SELECT *
    FROM main
    JOIN (
        SELECT '1' AS source, * FROM slave1
        UNION ALL
        SELECT '2', * FROM slave2
        UNION ALL
        ....
        ) AS slave ON ...

Сложнее вариант, когда результат должен быть развёрнут в сводную таблицу, т.е. для каждого пользователя в результате должна быть только одна строка. В этом случае задачу решает использование динамического SQL в формате хранимой процедуры.

Автор: RedLight 19.10.23, 07:23
Похоже без изучения теории не обойтись, буду читать про EAV до полного просветления :thanks:

Автор: Majestio 20.10.23, 07:00
Цитата RedLight @
Правильно ли я понял предложенный пример?

В принципе - да. В приведенном тобою коде "заведены" две категории и один пользователь. И пользователю "привязаны" "скилы" по этим категориям.

Цитата RedLight @
Текстовые названия категорий в моём случае не нужны, хорошо бы их избежать.

Названия категорий - некий аналог названий столбцов "плоской" таблицы. Не нужно этого избегать :) Но если уж избегать по-взрослому, тогда и имена пользователей нужно удалить, хватит их ID :lol:

Цитата RedLight @
и так же не ясно как сделать запрос на выборку пользователя с его данными из неё

Если честно - проще пареной репы. Но нужно, чтобы ты подумал - это полезно на будущее. Ибо ...

Цитата RedLight @
Похоже без изучения теории не обойтись, буду читать про EAV до полного просветления

Это правильное решение!

Автор: Akina 20.10.23, 07:04
Цитата RedLight @
Похоже без изучения теории не обойтись

Угу.

Цитата RedLight @
буду читать про EAV до полного просветления

Особое внимание - области применения и недостаткам, а также альтернативам.

Powered by Invision Power Board (https://www.invisionboard.com)
© Invision Power Services (https://www.invisionpower.com)