Несколько столбец подвыбирают в mysql 5 (5.1.42)
Этот, кажется, простая проблема, но я не могу заставить ее работать в единственном выборе или вложенном выборе. Получите авторов и (если таковые имеются) советники статьи (статья) в одну строку.
Я заказываю для объяснения проблемы, вот эти две (псевдо) таблицы данных
papers (id, title, c_year)
persons (id, firstname, lastname)
плюс таблица ссылки дополнительный (псевдо) атрибут w/one:
paper_person_roles(
paper_id
person_id
act_role ENUM ('AUTHOR', 'ADVISER')
)
Это - в основном список написанных работ (таблица: бумаги) и список штата и/или студентов (таблица: люди)
Статья мои имеют (1, N) авторов.
Статья может иметь (0, N) советников.
Человек может быть в роли 'АВТОРА' или 'СОВЕТНИКА' (но не одновременно).
Приложение в конечном счете производит строки таблицы, содержащие следующие записи:
TH: || Paper_ID | Author(s) | Title | Adviser(s) | TD: || 21334 |John Doe, Jeff Tucker|Why the moon looks yellow|Brown, Rayleigh| ...
Мой первый подход был похож:
выберите/извлеките полный список статей в приложение, например.
SELECT
q.id, q.title
FROM
papers AS q
ORDER BY
q.c_year
APPLICATION_LOOP(paper_ids in array)
SELECT
p.lastname, p.firstname, r.act_role
FROM
persons AS p, paper_person_roles AS r
WHERE
p.id=r.person_id AND r.paper_id = ?
# The application does further processing from here (pseudo):
foreach record from resulting records
if record.act_role eq 'AUTHOR' then join to author_column
if record.act_role eq 'ADVISER' then join to avdiser_column
end
print id, author_column, title, adviser_column
APPLICATION_LOOP
Я не являюсь очень опытным в нетривиальном SQL и не могу найти решение с синглом (объединенным или вложенным) избранным вызовом. Я попробовал sth. как
SELECT
q.title
(CONCAT_WS(' ',
(SELECT p.firstname, p.lastname AS aunames
FROM persons AS p, paper_person_roles AS r
WHERE q.id=r.paper_id AND r.act_role='AUTHOR')
)
) AS aulist
FROM
papers AS q, persons AS p, paper_person_roles AS r
Возможно, существует некоторый шанс?
Заранее спасибо
r.b.
2 ответа
Следующий запрос сработал с моими тестовыми данными, пожалуйста, попробуйте.
Два подзапроса необходимы для получения списка авторов/советников по каждой статье.
Select
p.id,
p.title,
p_aut.aut_name,
p_adv.adv_name
From papers p
Left Join (
Select pp_aut.paper_id,
Group_Concat(Concat(p_aut.firstname, ' ', p_aut.lastname)) aut_name
From paper_person_roles pp_aut
Join persons p_aut On (p_aut.id = pp_aut.person_id)
Where pp_aut.act_role='AUTHOR'
Group By pp_aut.paper_id
) p_aut On ( p_aut.paper_id = p.id )
Left Join (
Select pp_adv.paper_id,
Group_Concat(Concat(p_adv.firstname, ' ', p_adv.lastname)) adv_name
From paper_person_roles pp_adv
Join persons p_adv On (p_adv.id = pp_adv.person_id)
Where pp_adv.act_role='ADVISER'
Group By pp_adv.paper_id
) p_adv On ( p_adv.paper_id = p.id )
Group By p.id, p.title
По моему опыту, базы данных SQL не очень хорошо справляется с агрегированием подобных табличных данных в одну строку сжатых данных. В принципе, я думаю, что используемый вами подход хорош, однако другая альтернатива, которая приходит мне на ум, - это просто присоединиться к таблице лиц, чтобы вы вернули одну строку для каждого человека, который играет роль в данной статье.
Что-то вроде:
SELECT q.id, q.title, p.firstName, p.lastName, r.act_role FROM papers q, persons p,
paper_person_roles r where r.paper_id = q.id and r.person_id = p.id
Что для данного примера, который вы показали выше, даст вам следующие данные:
21334 |Why the moon looks yellow|John Doe |AUTHOR
21334 |Why the moon looks yellow|Jeff Tucker|AUTHOR
21334 |Why the moon looks yellow|Brown |ADVISER
21334 |Why the moon looks yellow|Rayleigh |ADVISER
и который достаточно легко проанализировать и получить конечный результат, который вы ищете.
С такими вещами все сводится к компромиссам:
- Вы тратите слишком много времени на то, чтобы снова и снова возвращаться к базе данных?
- Есть ли там тоже много данных, которые вы не можете объединить сразу?
- Ваша "оптимизация" делает ваш код слишком трудным для чтения?
Честно говоря, если ваш код работает так, как вы хотите, и вы еще не столкнулись с проблемами производительности, то оставьте все как есть и вернитесь к этому решению в тот день, когда вы начнете видеть снижение производительности по мере увеличения вашего набора данных.