MySQL 一对多 SQL SELECT 到单行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11441305/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
One-to-Many SQL SELECT into single row
提问by Grizz
I have data in two tables.
我有两个表中的数据。
The first table has a Primary Key called PKID
第一个表有一个名为 PKID 的主键
PKID DATA
0 myData0
1 myData1
2 myData2
The second table has the PKID column from table 1 as a foreign key
第二个表将表 1 中的 PKID 列作为外键
PKID_FROM_TABLE_1 U_DATA
0 unique0
0 unique1
0 unique2
1 unique3
1 unique4
1 unique5
2 unique6
2 unique7
2 unique8
The basic SELECT statement I am making now is
我现在所做的基本 SELECT 语句是
SELECT a.PKID, a.DATA, b.U_DATA
FROM table1 as a
INNER JOIN table2 as b
ON a.PKID = b.PKID_FROM_TABLE_1
This produces a table like this:
这会产生一个像这样的表：
PKID DATA U_DATA
0 myData0 unique0
0 myData0 unique1
0 myData0 unique2
1 myData1 unique3
1 myData1 unique4
1 myData1 unique5
2 myData2 unique6
2 myData2 unique7
2 myData2 unique8
What I would like is the following table:
我想要的是下表：
PKID DATA U_DATA1 U_DATA2 U_DATA3
0 myData0 unique0 unidque1 unique2
1 myData1 unique3 unidque4 unique5
2 myData2 unique6 unidque7 unique8
If it helps, each PKID will have exactly 3 entries in table2.
如果有帮助，每个 PKID 在表 2 中将正好有 3 个条目。
Is something like this possible in MySQL?
在 MySQL 中可能有这样的事情吗？
采纳答案by spencer7593
This is one way to get the result.
这是获得结果的一种方式。
This approach uses correlated subqueries. Each subquery uses an ORDER BYclause to sort the related rows from table2, and uses the LIMITclause to retrieve the 1st, 2nd and 3rd rows.
这种方法使用相关子查询。每个子查询使用一个ORDER BY子句对 table2 中的相关行进行排序，并使用该LIMIT子句检索第 1、第 2 和第 3 行。
SELECT a.PKID
, a.DATA
, (SELECT b1.U_DATA FROM table2 b1
WHERE b1.PKID_FROM_TABLE_1 = a.PKID
ORDER BY b1.U_DATA LIMIT 0,1
) AS U_DATA1
, (SELECT b2.U_DATA FROM table2 b2
WHERE b2.PKID_FROM_TABLE_1 = a.PKID
ORDER BY b2.U_DATA LIMIT 1,1
) AS U_DATA2
, (SELECT b3.U_DATA FROM table2 b3
WHERE b3.PKID_FROM_TABLE_1 = a.PKID
ORDER BY b3.U_DATA LIMIT 2,1
) AS U_DATA3
FROM table1 a
ORDER BY a.PKID
FOLLOWUP
跟进
@gliese581g points out that there may be performance issues with this approach, with a large number of rows returned by the outer query, since each subquery in the SELECT list gets executed for each row returned in the outer query.
@gliese581g 指出这种方法可能存在性能问题，外部查询返回大量行，因为 SELECT 列表中的每个子查询都会针对外部查询中返回的每一行执行。
It should go without saying that this approach cries out for an index:
不用说，这种方法需要一个索引：
ON table2 (PKID_FROM_TABLE_1, U_DATA)
-or, at a minimum-
- 或者，至少 -
ON table2 (PKID_FROM_TABLE_1)
It's likely the latter index already exists, if there's a foreign key defined. The former index would allow the query to be satisfied entirely from the index pages ("Using index"), without the need for a sort operation ("Using filesort").
如果定义了外键，则后一个索引可能已经存在。前一个索引将允许完全从索引页（“使用索引”）中满足查询，而不需要排序操作（“使用文件排序”）。
@glies581g is quite right to point out that performance of this approach can be problematic on "large" sets.
@glies581g 指出这种方法的性能在“大型”集合上可能存在问题，这是非常正确的。
回答by sdfor
Depending on your release of MySQL, you can look into GROUP_CONCAT
根据您的 MySQL 版本，您可以查看 GROUP_CONCAT
