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

How to get multiple records against one record based on relation?

提问by mdahlman

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:

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

Org_ID      Org_Address    Org_OtherDetails    Employess

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

回答by mdahlman

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.

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

• MySQL- Probably the simplest solution. Use the built-in GROUP_CONCAT function. In your example you would want something like this:

• MySQL- 可能是最简单的解决方案。使用内置的 GROUP_CONCAT 函数。在你的例子中，你会想要这样的东西：

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- EDIT:PostgreSQL 9.0 is equally simple now that string_agg(expression, delimiter) is built-in. Here it is with 'comma-space' between elements:

• PostgreSQL-编辑：PostgreSQL 9.0 现在同样简单，因为 string_agg(expression, delimiter) 是内置的。这是元素之间的“逗号空间”：

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 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 postfor more details. (Of course PostgreSQL 9.0 and later have this option as well.)

9.0 之前的 PostgreSQL 允许您使用 CREATE AGGREGATE 定义自己的聚合函数。比 MySQL 稍微多一些工作，但更灵活。有关更多详细信息，请参阅此另一篇文章。（当然 PostgreSQL 9.0 及更高版本也有这个选项。）

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

• Oracle & MS SQL Server- 创建一个以 org_id 作为输入并输出串联员工姓名的存储过程。然后在您的查询中使用此存储过程。此处的其他一些回复包括有关如何编写此类存储过程的一些详细信息。

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

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

• 其他 DBMS 技术- 存储过程路由是最有可能的。也许其他人可以用更多技术特定的答案来更新这个答案。

回答by igelkott

Since the question is tagged as MySQL, you should be able to use a MySQL-specific solution, namely, GROUP_CONCAT. For example,

由于问题被标记为 MySQL，因此您应该能够使用特定于 MySQL 的解决方案，即 GROUP_CONCAT。例如，

select Org_ID, Org_Address, Org_OtherDetails,
       GROUP_CONCAT(employees) as Employees
from  employees a, organization b
where a.org_id=b.org_id
group by b.org_id;

回答by Adam

in MS SQL you can do:

在 MS SQL 中，您可以执行以下操作：

create function dbo.table2list (@input int)
returns varchar(8000)
as
BEGIN
declare @putout varchar(8000)
set @putout = ''
select @putout = @putout + ', ' + <employeename>
from <employeetable>
where <orgid> = @input
return @putout
end

then do:

然后做：

select * from org, dbo.table2list(orgid)
from <organisationtable>

I think you can do it with COALESCE() as well, but can't remember the syntax off the top of my head

我想你也可以用 COALESCE() 来做到这一点，但我脑子里想不起来语法

回答by AJ.

The short answer is "no".

最简洁的答案是不”。

As noted in other answers, there are vendor-specific ways to achieve this result, but there is no pure SQL solution which works in one query.

正如其他答案中所指出的，有特定于供应商的方法可以实现这一结果，但没有可以在一个查询中工作的纯 SQL 解决方案。

sorry about that :(

对于那个很抱歉 ：（

Presumably one of the vendor specific solutions will work for you?

据推测，供应商特定的解决方案之一适合您？

回答by michal kralik

Here's what you can do, you have 2 options:

这是您可以执行的操作，您有 2 个选择：

select *
FROM
  users u
  LEFT JOIN organizations o ON (u.idorg = o.id);

This way you will get extra data on each row - full organization info you don't really need.

通过这种方式，您将获得每一行的额外数据 - 您并不真正需要的完整组织信息。

Or you can do:

或者你可以这样做：

select o.*, group_concat(u.name)
FROM
  users u
  LEFT JOIN organizations o ON (u.idorg = o.id)
GROUP BY
  o.id

http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

The second approach is applicable if you want to see ie. list of usernames "user1, user2, user3", but don't want to operate on the fields themselves...

如果您想查看 ie，则第二种方法适用。用户名列表“user1、user2、user3”，但不想自己对字段进行操作...

回答by Mike McAllister

If you use Oracle you can create a PL/SQL function you can use in your query that accepts an organization_id as input, and returns the first name of all employees belonging to that org as a string. For example:-

如果您使用 Oracle，您可以创建一个可以在您的查询中使用的 PL/SQL 函数，该函数接受一个 organization_id 作为输入，并以字符串形式返回属于该组织的所有员工的名字。例如：-

select
   o.org_id,
   o.org_address,
   o.org_otherdetails,
   org_employees( o.org_id ) as org_employees
from
   organization o

回答by Aaron

It all depends. If you do a join, you get all the organization data on every row. (1 row per employee). That has a cost. If you do two queries. (Org and Emp) that has a different cost.

这一切都取决于。如果进行联接，则会获得每一行的所有组织数据。（每位员工 1 行）。这是有代价的。如果你做两个查询。(Org 和 Emp) 具有不同的成本。

Pick your poison.

选择你的毒药。

回答by Orlando Colamatteo

For SQL Server SQLCLR aggregates in this project are inspired by the MSDN code sample however they perform much better and allow for sorting (as strings) and alternate delimiters if needed. They offer almostequivalent functionality to MySQL's GROUP_CONCAT for SQL Server.

对于 SQL Server，此项目中的 SQLCLR 聚合受 MSDN 代码示例的启发，但它们的性能要好得多，并且允许排序（作为字符串）和备用分隔符（如果需要）。它们为 SQL Server提供了与 MySQL 的 GROUP_CONCAT几乎相同的功能。

A full comparison of the advantages/disadvantages of the CLR aggregates and the FOR XML solution can be found in the documentation:

可以在文档中找到 CLR 聚合和 FOR XML 解决方案的优缺点的完整比较：

http://groupconcat.codeplex.com

http://groupconcat.codeplex.com