sql - mysql 5 (5.1.42) 中的多列子选择

Question

这似乎是一个简单的问题，但我不能在一个单一的选择或嵌套选择。检索作者和(如果有的话)顾问将纸(文章)排成一行。

我为了说明问题，这里是两个数据表(伪)

papers (id, title, c_year)
persons (id, firstname, lastname)

加上一个带有一个额外属性的链接表(伪):

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|...

My first approach was like:
select/extract a full list of articles into the application, eg.

SELECT 
   q.id, q.title
FROM 
   papers AS q
ORDER BY 
   q.c_year

并将查询结果保存到数组中(在应用程序中)。在这之后步骤，遍历返回信息的数组并检索作者和顾问(如果有的话)，通过链接表中的准备好的声明(？是论文的 ID)喜欢:

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 不是很精通，找不到具有单个(组合或嵌套)选择调用的解决方案。我试过……喜欢

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.

Answer 1

以下查询适用于我的测试数据，请试一试。

这两个子查询是获取每篇论文的作者/顾问列表所必需的。

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