Присоединитесь к первым 3 записям в каждой группе и объедините их как одну строку

Один user имеет много qualifications. Мне нужно создать электронная таблица в Excel из базы данных MySQL. Результат должен содержать строку для каждого пользователя со столбцами для их 1-й, 2-й и 3-й квалификации в одной строке.

Таблица пользователей

id  |  Name     
--------------
1   |  James
2   |  Bob
3   |  Adam
4   |  Michael

Таблица квалификаций

id  |  user_id  |  subject
-----------------------------------------
1   |  1        |  English
2   |  2        |  History
3   |  2        |  Mathematics
4   |  2        |  Biology
5   |  2        |  Sports Science
6   |  2        |  Art
7   |  3        |  Physics
8   |  3        |  Chemistry
10  |  4        |  Geography
11  |  4        |  Computer Science
12  |  4        |  Theology

ЖЕЛАЕМЫЙ РЕЗУЛЬТАТ

User ID  |  Name     |  Qualification 1  |  Qualification 2   |  Qualification 3
-----------------------------------------------------------------------
1        |  James    |  English          |  NULL              |  NULL
2        |  Bob      |  History          |  Mathematics       |  Biology
3        |  Adam     |  Physics          |  Chemistry         |  NULL
4        |  Michael  |  Geography        |  Computer Science  |  Theology

Примечания

  • Мне нужны только первые 3 квалификации для каждого пользователя.
  • Пользователь может иметь любое количество квалификаций. У них может их не быть, у них может быть 10.
  • В этом примере у Джеймса только 1 квалификация. У Боба 5. У Адама только 2.
  • Мне нужно только решение SQL, но конечной целью является экспорт в Excel (не имеет значения, если в Excel есть что-то, что я могу использовать, чтобы упростить эту задачу).
  • Это разовая задача. Мне не нужно делать это регулярно.

Если вы создаете csv, вы также можете использовать group_concat с ',' между полями для этого.

T Gray 13.09.2018 19:29
Освоение архитектуры микросервисов с Laravel: Лучшие практики, преимущества и советы для
Освоение архитектуры микросервисов с Laravel: Лучшие практики, преимущества и советы для
В последние годы архитектура микросервисов приобрела популярность как способ построения масштабируемых и гибких приложений. Laravel , популярный PHP...
Как построить CRUD-приложение в Laravel
Как построить CRUD-приложение в Laravel
Laravel - это популярный PHP-фреймворк, который позволяет быстро и легко создавать веб-приложения. Одной из наиболее распространенных задач в...
Освоение PHP и управление базами данных: Создание собственной СУБД - часть II
Освоение PHP и управление базами данных: Создание собственной СУБД - часть II
В предыдущем посте мы создали функциональность вставки и чтения для нашей динамической СУБД. В этом посте мы собираемся реализовать функции обновления...
Документирование API с помощью Swagger на Springboot
Документирование API с помощью Swagger на Springboot
В предыдущей статье мы уже узнали, как создать Rest API с помощью Springboot и MySql .
Роли и разрешения пользователей без пакета Laravel 9
Роли и разрешения пользователей без пакета Laravel 9
Этот пост изначально был опубликован на techsolutionstuff.com .
Как установить LAMP Stack - Security 5/5 на виртуальную машину Azure Linux VM
Как установить LAMP Stack - Security 5/5 на виртуальную машину Azure Linux VM
В предыдущей статье мы завершили установку базы данных, для тех, кто не знает.
1
1
38
2
Перейти к ответу Данный вопрос помечен как решенный

Ответы 2

Ответ принят как подходящий

Самый простой метод (до MySQL 8.0) использует переменные и условное агрегирование:

select u.user_id, u.name,
       max(case when rn = 1 then q.subject end) as qualification_1,
       max(case when rn = 2 then q.subject end) as qualification_2,
       max(case when rn = 3 then q.subject end) as qualification_3
from users u left join
     (select q.*,
             (@rn := if (@u = q.user_id, @rn + 1,
                        if (@u := q.user_id, 1, 1)
                       )
             ) as rn
      from qualifications q,
           (select @u := -1, @rn := 0) params
      order by q.user_id, q.id
     ) q
     on u.id = q.user_id
where rn <= 3
group by u.id, u.name

Спасибо. Я получил сообщение об ошибке на (select @u := -1, @rn := 0) params из-за отсутствия запятой, я думаю (поправьте меня, если я ошибаюсь). Думаю, теперь я смогу с этим поработать, спасибо.

BadHorsie 13.09.2018 18:20

Вы можете использовать функции GROUP_CONCAT и SUBSTRING_INDEX. Три предмета могут появиться в одном столбце:

SELECT
    users.id,
    users.name,
    SUBSTRING_INDEX(GROUP_CONCAT(qualifications.subject SEPARATOR ','), ',', 3) AS subjects
FROM users
LEFT JOIN qualifications ON users.id = qualifications.user_id
GROUP BY users.id, users.name

Или вы можете разделить их:

SELECT
    id,
    name,
    subjects,
    SUBSTRING_INDEX(subjects, ',', 1) AS subject1,
    SUBSTRING_INDEX(SUBSTRING_INDEX(subjects, ',', 2), ',', -1) AS subject2,
    SUBSTRING_INDEX(SUBSTRING_INDEX(subjects, ',', 3), ',', -1) AS subject3
FROM (
    SELECT
        users.id,
        users.name,
        CONCAT(GROUP_CONCAT(qualifications.subject SEPARATOR ','), ',,') AS subjects
    FROM users
    LEFT JOIN qualifications ON users.id = qualifications.user_id
    GROUP BY users.id, users.name
) AS x

Другие вопросы по теме