Получение данных из нескольких таблиц в одну строку при объединении некоторых значений
Я пытаюсь извлечь данные из таблиц и объединить несколько строк в один столбец, не повторяя никакой информации.
У меня есть следующие таблицы: профиль, квалификация, проекты.
Профиль pro_id фамилия имя ------ ------- ---------- 1 Джон Джеймс 2 король Фред 3 Luxury-Yachts Raymond Квалификация pro_id Степень учебного года ------ ------ ------ ----- 1 MBA Уортонский университет 2002 1 LLB Йельский университет 2001 2 Бакалавриат Ковингтонского университета 1998 2 BED Kellog University 1995 проектов pro_id Название Год ------ ------ ------ 1 Социальная сеть 2003 1 Раскопки водного мусора 2007 2 Дизайн солнечных радиоприемников 1992 2 Разработка экспертных систем 2011
Я хочу получить всю информацию для каждого человека, причем каждый человек появляется только один раз в результате. Информация о квалификациях и проектах должна быть в отдельной колонке (одна колонка для квалификаций, другая для проектов), разделенных запятыми. Например, результаты для приведенных выше образцов данных должны быть:
1 Университет Джона Джеймса MBA Wharton 2002, LLB Йельский университет 2001 Социальные сети 2003, Раскопки водного мусора 2007, Дизайн солнечных панелей 2008 2 King Fred BSc Университет Ковингтона, 1998, Университет Беда Келлога, 1995, MSC MIT 2011 Проектирование солнечных радиостанций 1992, Разработка экспертных систем 2011 3 Raymond Luxury-Yachts
В настоящее время у меня есть запрос:
SELECT pro_id,
surname,
firstname,
group_concat(degree,school,year) AS qual,
concat(Title,year) AS work
FROM profile,
LEFT JOIN qualification
ON qualification.pro_id = profile.pro_id
JOIN projects
ON projects.pro_id = profile.pro_id
GROUP BY pro_id
Для примера данных этот запрос приводит к:
1 Университет Джона Джеймса MBA Wharton 2002, Социальные сети 2003 1 Джон Джеймс LLB Йельский университет 2001, Раскопки водного мусора 2007 1 Университет Джона Джеймса MBA Wharton 2002, Социальные сети 2003, Раскопки водного мусора 2007 так далее
Примечание: Raymond Luxury-Yachts отсутствует в текущем результате.
Я не хочу дублировать записи результатов. Также, если у фамилии нет записи в таблице квалификаций и проектов, я хочу, чтобы запрос возвращал имя и отображал пустое поле в таблице квалификаций и проектов, а не пропускал их вообще.
3 ответа
Я думаю, что вы близки к своим мыслям о group_concat. Однако с возможными значениями No (таким образом, оставляя нулевые значения), могут возникнуть проблемы. Я хотел бы, чтобы каждая вторичная таблица была предварительно конкатенацирована по идентификатору человека и присоединялась к ЭТОМУ результату. Устраняет проблему нулей
SELECT
p.pro_id,
p.surname,
p.firstname,
PreQConcat.UserQual,
PrePJConcat.UserWork
FROM
profile p
LEFT JOIN
( select q.pro_id,
group_concat( q.degree, q.school, q.year) AS UserQual
from
qualification q
group by
q.pro_id ) PreQConcat
ON p.Pro_ID = PreQConcat.pro_id
LEFT JOIN
( select pj.pro_id,
concat(pj.Title, pj.year) AS UserWork
from
projects pj
group by
pj.pro_id ) PrePJConcat
ON p.Pro_ID = PrePJConcat.pro_id
В любом случае, вы проходите через всех людей и хотите, чтобы все их соответствующие элементы (если они существуют) были сгруппированы, так почему группируйте, если существует вероятность, что этого не существует. Пусть запросы JOINED выполняются по одному разу, завершаются одним результатом, сгруппированным только по тем людям, для которых у него были данные, а затем присоединяются к исходному профилю.
Использование Join решит проблему с отображением значений, даже если в таблице проектов нет записей. По первому вопросу вы можете попробовать создать хранимую функцию и вызвать ее из оператора select. Эта функция примет pro_id в качестве параметра, создаст объединенную строку и вернет ее. Это единственное решение для MySQL, о котором я могу думать в данный момент.
Заменить LEFT JOIN на JOIN
Select pro_id, surname, firstname, group_concat(degree,school,year) as qual,concat(Title,year) as work
from profile
join qualification on qualification.pro_id = profile.pro_id
join projects on projects.pro_id = profile.pro_id group by pro_id