一对多SQL SELECT成单行

问题描述

我在两个表中都有数据.

I have data in two tables.

第一个表具有一个称为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在表2中将恰好具有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.

此方法使用相关的子查询.每个子查询使用子句对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

---

关注

---

FOLLOWUP

@ 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成单行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！