如何根据关系获取针对一条记录的多条记录?

2021-11-20 00:00:00 sql database mysql

我有两个表 Organization 和 Employee 具有一对多的关系，即一个组织可以有多个员工.现在我想选择特定组织的所有信息以及该组织的所有员工的名字.最好的方法是什么?我可以在单个记录集中获得所有这些吗，或者我将不得不根据 no 获得多行.员工?这是我想要的一些图形演示:

I have two tables Organisation and Employee having one to many relation i.e one organisation can have multiple employees. Now I want to select all information of a particular organisation plus first name of all employees for this organisation. What’s the best way to do it? Can I get all of this in single record set or I will have to get multiple rows based on no. of employees? Here is a bit graphical demonstration of what I want:

Org_ID      Org_Address    Org_OtherDetails    Employess

1           132A B Road    List of details     Emp1, Emp2, Emp3.....

推荐答案

最初的问题是特定于数据库的，但也许这是包含更通用答案的好地方.这是一个常见的问题.您所描述的概念通常称为组连接".SQL-92 或 SQL-99 中没有标准解决方案.因此，您需要一个特定于供应商的解决方案.

The original question was database specific, but perhaps this is a good place to include a more generic answer. It's a common question. The concept that you are describing is often referred to as 'Group Concatenation'. There's no standard solution in SQL-92 or SQL-99. So you'll need a vendor-specific solution.

  • MySQL - 使用内置的 GROUP_CONCAT 函数.在你的例子中，你会想要这样的东西:
  • MySQL - Use the built-in GROUP_CONCAT function. In your example you would want something like this:
select 
  o.ID, o.Address, o.OtherDetails,
  GROUP_CONCAT( concat(e.firstname, ' ', e.lastname) ) as Employees
from 
  employees e 
  inner join organization o on o.org_id=e.org_id
group by o.org_id

  • PostgreSQL - PostgreSQL 9.0 现在同样简单，因为 string_agg(expression, delimiter) 是内置的.这是元素之间的逗号空格":

    • PostgreSQL - PostgreSQL 9.0 is equally simple now that string_agg(expression, delimiter) is built-in. Here it is with 'comma-space' between elements:

      • select 
  o.ID, o.Address, o.OtherDetails,
  STRING_AGG( (e.firstname || ' ' || e.lastname), ', ' ) as Employees
from 
  employees e 
  inner join organization o on o.org_id=e.org_id
group by o.org_id

      9.0 之前的 PostgreSQL 允许您使用 CREATE AGGREGATE 定义自己的聚合函数.比 MySQL 稍微多一些工作，但更灵活.请参阅此 其他帖子 了解更多详情.(当然 PostgreSQL 9.0 及更高版本也有这个选项.)

      PostgreSQL before 9.0 allows you to define your own aggregate functions with CREATE AGGREGATE. Slightly more work than MySQL, but much more flexible. See this other post for more details. (Of course PostgreSQL 9.0 and later have this option as well.)

      • Oracle - 使用 LISTAGG 的相同想法.

      • Oracle - same idea using LISTAGG.

      MS SQL Server - 使用 STRING_AGG

      后备解决方案 - 在其他数据库技术或上面列出的技术的非常非常旧的版本中，您没有这些组连接功能.在这种情况下，创建一个将 org_id 作为其输入并输出串联员工姓名的存储过程.然后在您的查询中使用此存储过程.此处的其他一些回复包括有关如何编写此类存储过程的一些详细信息.

      Fallback solution - in other database technologies or in very very old versions of the technologies listed above you don't have these group concatenation functions. In that case create a stored procedure that takes the org_id as its input and outputs the concatenated employee names. Then use this stored procedure in your query. Some of the other responses here include some details about how to write stored procedures like these.

      select 
  o.ID, o.Address, o.OtherDetails,
  MY_CUSTOM_GROUP_CONCAT_PROCEDURE( o.ID ) as Employees
from 
  organization o

相关文章