mysql - keys - One-to-Many SQL SELECT en una sola fila

mysql workbench foreign keys

Question

Tengo datos en dos tablas.

La primera tabla tiene una clave primaria llamada PKID

PKID  DATA
0    myData0
1    myData1
2    myData2

La segunda tabla tiene la columna PKID de la tabla 1 como una clave externa

PKID_FROM_TABLE_1  U_DATA
       0          unique0
       0          unique1        
       0          unique2
       1          unique3
       1          unique4
       1          unique5
       2          unique6
       2          unique7
       2          unique8

La instrucción básica SELECT que estoy haciendo ahora es

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

Esto produce una tabla como esta:

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

Lo que me gustaría es la siguiente tabla:

PKID   DATA    U_DATA1    U_DATA2    U_DATA3
 0     myData0 unique0    unidque1   unique2
 1     myData1 unique3    unidque4   unique5
 2     myData2 unique6    unidque7   unique8

Si ayuda, cada PKID tendrá exactamente 3 entradas en la tabla 2.

¿Es algo así posible en MySQL?

Answer 1

Dependiendo de su versión de MySQL, puede buscar en GROUP_CONCAT

Answer 2

Esta es una forma de obtener el resultado.

Este enfoque utiliza subconsultas correlacionadas. Cada subconsulta usa una cláusula ORDER BY para ordenar las filas relacionadas de la tabla 2, y usa la cláusula LIMIT para recuperar las filas 1, 2 y 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  

SEGUIR

@ gliese581g señala que puede haber problemas de rendimiento con este enfoque, con un gran número de filas devueltas por la consulta externa, ya que cada subconsulta en la lista SELECT se ejecuta para cada fila devuelta en la consulta externa.

No hace falta decir que este enfoque clama por un índice:

ON table2 (PKID_FROM_TABLE_1, U_DATA)

-o, como mínimo-

ON table2 (PKID_FROM_TABLE_1)

Es probable que el último índice ya exista, si hay una clave externa definida. El índice anterior permitiría que la consulta se satisficiera completamente desde las páginas de índice ("Uso del índice"), sin la necesidad de una operación de ordenación ("Uso del archivo").

@ glies581g tiene toda la razón al señalar que el rendimiento de este enfoque puede ser problemático en conjuntos "grandes".