mysql查询动态表_MySQL透视表与动态列的查询

我正在使用下表存储产品数据：

mysql> SELECT * FROM product;

+---------------+---------------+--------+

| id | name | description | stock |

+---------------+---------------+--------+

| 1 | product1 | first product | 5 |

| 2 | product2 | second product| 5 |

+---------------+---------------+--------+

mysql> SELECT * FROM product_additional;

+-----------------+------------+

| id | fieldname | fieldvalue |

+-----------------+------------+

| 1 | size | S |

| 1 | height | 103 |

| 2 | size | L |

| 2 | height | 13 |

| 2 | color | black |

+-----------------+------------+

使用以下查询从两个表中选择记录

mysql> SELECT

p.id

, p.name

, p.description

,MAX(IF(pa.fieldname = 'size', pa.fieldvalue, NULL)) as `size`

,MAX(IF(pa.fieldname = 'height', pa.fieldvalue, NULL)) as `height`

,MAX(IF(pa.fieldname = 'color', pa.fieldvalue, NULL)) as `color`

FROM product p

LEFT JOIN product_additional AS pa ON p.id = pa.id

GROUP BY p.id

+---------------+---------------+--------+---------+--------+

| id | name | description | size | height | color |

+---------------+---------------+--------+---------+--------+

| 1 | product1 | first product | S | 103 | null |

| 2 | product2 | second product| L | 13 | black |

+---------------+---------------+--------+---------+--------+

一切都正常工作:)

因为我动态地填充了“附加”表，所以如果查询也是动态的，那就很好了。这样，我不必每次输入新的字段名和字段值就更改查询。