Lo siento por preguntar esto, así que tengo 2 tablas:
tabla 1 "coche"

car_id   car_name
   1      toyota
   2      honda

Tabla 2 "incidente"

inc_id   car_id    status    date_status  date_created
   1        1      arrive    2020-07-26    2020-07-26
   2        1      repair    2020-07-27    2020-07-27
   3        1      finish    2020-07-28    2020-07-28

Necesito esos datos para mostrar así:

car_id     car_name     status_1    date_1    status_2    date_2     status_3    date_3
   1         toyota      arrive   2020-07-26  repair    2020-07-27    finish   2020-07-28

He intentado esto:

SELECT
a.car_id,a.car_name,
(SELECT status,date_status FROM incident WHERE status= 'arrive') AS arrive,
(SELECT status,date_status FROM incident WHERE status= 'repairs') AS repairs,
(SELECT status,date_status FROM incident WHERE status= 'finish') AS finish,
FROM car a
LEFT OUTER JOIN incident b ON a.car_id = b.car_id
WHERE b.date_created BETWEEN '2020-07-27' AND '2020-07-28';

Pero su devuelve Código de error: 1242 Subconsulta devuelve más de 1 fila ,

1
Tray Scott 26 jul. 2020 a las 14:13

2 respuestas

La mejor respuesta

Debe agrupar por automóvil y usar agregación condicional:

SELECT c.car_id, c.car_name,
      'arrive' status_1,
      max(case when i.status = 'arrive' then date_status end) date_1,      
      'repair' status_2,
      max(case when i.status = 'repair' then date_status end) date_2,
      'finish' status_3,
      max(case when i.status = 'finish' then date_status end) date_3
FROM car c LEFT OUTER JOIN incident i 
ON c.car_id = i.car_id
GROUP BY c.car_id, c.car_name

Si agrega la cláusula WHERE:

WHERE b.date_created BETWEEN '2020-07-27' AND '2020-07-27'

Cualquier fila no coincidente entre las tablas será rechazada y la unión será en realidad una INNER unión.
Entonces, si no necesita filas no coincidentes, entonces está bien, pero si las quiere, agregue la condición en la cláusula ON:

.....................
FROM car c LEFT OUTER JOIN incident i 
ON c.car_id = i.car_id
AND i.date_created BETWEEN '2020-07-27' AND '2020-07-27'
.....................
0
forpas 26 jul. 2020 a las 11:36

Cambie su consulta a esto:

SELECT
    c.car_id,
    c.car_name,
    i.first_arrived,
    i.first_repairs,
    i.first_finish
FROM
    car AS c
    LEFT OUTER JOIN
    (
        SELECT
            car_id
            MIN( CASE WHEN status = 'arrive'  THEN date_status END ) AS first_arrived,
            MIN( CASE WHEN status = 'repairs' THEN date_status END ) AS first_repairs,
            MIN( CASE WHEN status = 'finish'  THEN date_status END ) AS first_finish
        FROM
            incident
        GROUP BY
            car_id
    ) AS i ON c.car_id = i.car_id

Sin embargo, no es posible producir su conjunto de columnas deseado original de status_1, date_1, status_2, date_2, status_3, date_3 con la información que me ha proporcionado porque podría haber muchos otros valores distintos (¡y duplicados!) status, y los datos siempre deben ser basado en filas, no basado en columnas. Cualquier lógica UNPIVOT debe estar en su código de aplicación, no SQL.

0
Dai 26 jul. 2020 a las 11:22