一对多查询 [英] One-to-many query

问题描述

我有一个假设的数据库，因为下图是每种植物最多可以有 4 种颜色.

I have an hypothetical database as the image below were each plant can have a maximum of 4 colours.

我希望能够以类似于以下的格式返回我的结果.

I wish to be able to return my results in a format similar to the below.

如果我使用 Inner Join 运行标准查询，结果会重复，因为植物有不止一种颜色.因此，我尝试运行多个单独的查询，首先返回植物，然后使用新查询返回颜色.然后我循环颜色结果以产生我想要的输出.

If I run a standard query with Inner Join the results are duplicated were the plant has more than one colour. I have therefore tried running multiple separate queries were I first return the plant then a new query to return the colours. I then loop though colour result to produce the output I desire.

我认为实现这一目标的有效方法还有很长的路要走吗?

I assume there is a far for efficient way to achieve this?

我正在尝试将其作为查询和存储过程来执行，因此非常感谢任何指针.

I am trying to do this as a query and as a stored procedure so any pointers would be much appreciated.

推荐答案

如果你知道最多有4种颜色，那么你可以使用row_number()和条件聚合:

If you know there are a maximum of 4 colors, then you can use row_number() and conditional aggregation:

select plantname,
       max(case when seqnum = 1 then colorname end) as color_1,
       max(case when seqnum = 2 then colorname end) as color_2,
       max(case when seqnum = 3 then colorname end) as color_3,
       max(case when seqnum = 4 then colorname end) as color_4
from (select p.plantname, p.plantid, c.colorname,
             row_number() over (partition by p.plantid order by c.colorid) as seqnum
      from plants p join
           plantcolors pc
           on pc.plantid = p.plantid join
           colors c
           on pc.colorid = c.colorid
     ) pc
group by plantname, plantid;

这篇关于一对多查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！