代码之家 › 专栏 › 技术社区 › rubber boots

mysql 5(5.1.42)中的多列嵌套select

subquery mysql sql

rubber boots · 技术社区 · 16 年前

这个似乎是个简单的问题,但我做不到 在单一选择或嵌套选择 . 检索把纸(文章)排成一行。

我想解释一下这个问题,这是两个数据表(伪)

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

我的第一个方法是:
在应用程序中选择/提取文章的完整列表,例如

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调用的解决方案。我尝试某事

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 回复 | 直到 16 年前

Peter Lang 16 年前

下面的查询使用了我的测试数据,请尝试一下。

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

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

BryanD 16 年前

在我的经验中,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

这很容易解析为您要查找的最终结果。

有了这些东西,一切都是为了权衡:
-你是不是花了太多时间一次又一次地返回数据库?
-是否有过多的数据不能一次全部加入?
-你的“优化”最终会使你的代码太难阅读吗?

坦率地说,如果您的代码按照您想要的方式工作,而且您还没有遇到性能问题,那么请保持原样,并在您开始看到随着数据集的扩展性能降低的那一天回到这个决定。