MySQL 用其他表的多个结果构造一个结果

Question

我承认这是一个糟糕的标题，但我认为这应该澄清。假设我有一个如下所示的车辆表

Vehicles

| vehicle_id | vehicle_name | vehicle_price |
| 1          | pinto        | 70000         |

Vehicle Options 

| vehicle_id | option_name  | option_value  |
| 1          | color        | red           |
| 1          | engine       | v8            |

我怎样才能将所有这些作为一行返回...基本上是车辆行及其附加的选项。

| vehicle_id = 1 | vehicle_name = pinto | option_value = red | option_value(renamed) = V8 |

Answer 1

您想要PIVOT MySQL 没有默认功能的记录。但您仍然可以使用 MAX() 和 CASE 语句手动执行此操作。

SELECT  a.vehicle_id,
        a.vehicle_name,
        a.vehicle_price,
        MAX(CASE WHEN option_name = 'color' THEN option_value END) color,
        MAX(CASE WHEN option_name = 'engine' THEN option_value END) engine
FROM    Vehicles a
        INNER JOIN VehicleOptions b
            ON a.vehicle_id = b.vehicle_id 
GROUP   BY  a.vehicle_id,
            a.vehicle_name,
            a.vehicle_price

• SQLFiddle Demo

输出

╔════════════╦══════════════╦═══════════════╦═══════╦════════╗
║ VEHICLE_ID ║ VEHICLE_NAME ║ VEHICLE_PRICE ║ COLOR ║ ENGINE ║
╠════════════╬══════════════╬═══════════════╬═══════╬════════╣
║          1 ║ pinto        ║         70000 ║ red   ║ v8     ║
╚════════════╩══════════════╩═══════════════╩═══════╩════════╝