mysql - 如何根据字段的值选择不同的列

Question

我有两个表 products 和 dimensions。 dimensions 表包含以下字段:product_id、name、value、unit。

我想联接这些表，以便每个维度名称在结果中显示为单独的列。例如。我在尺寸表中有以下条目:

1, width, 4, inches
2, width, 5, inches
1, length, 10, inches
2, height, 7, inches

我希望结果是这样的:

product id, length, length unit, width, width unit, height, height unit
1             10      inches       4       inches
2                                  5       inches     7       inches

我如何使用 sql 查询实现此目的？

Answer 1

您可以尝试将其用于 LEFT JOIN 语句。

例如

SELECT p.id, l.value AS length, l.unit AS length_unit, w.value AS width, w.unit AS width_unit, h.value AS height, h.unit AS height_unit
FROM products AS p
LEFT JOIN dimensions AS l ON (p.id = l.product_id AND l.name = 'length')
LEFT JOIN dimensions AS w ON (p.id = w.product_id AND w.name = 'width')
LEFT JOIN dimensions AS h ON (p.id = h.product_id AND h.name = 'height')