如何根据关系获取多条记录？ [英] How to get multiple records against one record based on relation?

问题描述

我有两个表组织和员工有一对多的关系，即一个组织可以有多个雇员。现在我想选择特定组织的所有信息加上该组织的所有员工的名字。什么是最好的方法呢？我可以在单个记录集中获得所有这一切，或者我将不得不基于否获取多行。的员工？下面是我想要的图形演示：

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 - Probably the simplest solution. 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 strong> EDIT： PostgreSQL 9.0同样简单，现在string_agg（表达式，分隔符）是内置的。这里是元素之间的逗号空格：


• 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

PostgreSQL 9.0之前允许您定义自己的聚合函数与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& MS SQL Server - 创建一个存储过程，它将org_id作为其输入，并输出连接的员工姓名。然后在查询中使用此存储过程。其他一些响应包括一些有关如何编写这些存储过程的详细信息。


• Oracle & MS SQL Server - 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







• 其他DBMS技术 - 存储过程路由是最可能的。也许其他人可以通过更具体的技术更新此答案。


• Other DBMS technologies - The stored procedure route is the most likely. Perhaps others can update this answer with more technology specific answers.

这篇关于如何根据关系获取多条记录？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！