Один 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
Примечания
Самый простой метод (до 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
из-за отсутствия запятой, я думаю (поправьте меня, если я ошибаюсь). Думаю, теперь я смогу с этим поработать, спасибо.
Вы можете использовать функции 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
Если вы создаете csv, вы также можете использовать group_concat с ',' между полями для этого.