问题描述
我有两个表中的数据.
第一个表有一个名为 PKID 的主键
The first table has a Primary Key called PKID
PKID DATA
0 myData0
1 myData1
2 myData2
第二个表将表 1 中的 PKID 列作为外键
The second table has the PKID column from table 1 as a foreign key
PKID_FROM_TABLE_1 U_DATA
0 unique0
0 unique1
0 unique2
1 unique3
1 unique4
1 unique5
2 unique6
2 unique7
2 unique8
我现在做的基本 SELECT 语句是
The basic SELECT statement I am making now is
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
我想要的是下表:
PKID DATA U_DATA1 U_DATA2 U_DATA3
0 myData0 unique0 unidque1 unique2
1 myData1 unique3 unidque4 unique5
2 myData2 unique6 unidque7 unique8
如果有帮助，每个 PKID 将在 table2 中恰好有 3 个条目.
If it helps, each PKID will have exactly 3 entries in table2.
这样的事情在 MySQL 中是否可行?
Is something like this possible in MySQL?
推荐答案
这是获得结果的一种方式.
This is one way to get the result.
此方法使用相关子查询.每个子查询使用 ORDER BY 子句对 table2 中的相关行进行排序，并使用 LIMIT 子句检索第一、第二和第三行.
This approach uses correlated subqueries. Each subquery uses an ORDER BY clause to sort the related rows from table2, and uses the LIMIT clause to retrieve the 1st, 2nd and 3rd rows.
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
<小时>
跟进
@gliese581g 指出这种方法可能存在性能问题，外部查询返回大量行，因为 SELECT 列表中的每个子查询都会针对外部查询中返回的每一行执行.
@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.
不用说，这种方法需要索引:
It should go without saying that this approach cries out for an index:
ON table2 (PKID_FROM_TABLE_1, U_DATA)
-或者，至少-
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 非常正确地指出，这种方法的性能在大型"集合上可能会出现问题.
@glies581g is quite right to point out that performance of this approach can be problematic on "large" sets.
这篇关于一对多 SQL SELECT 到单行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，WP2