时间戳

首页 > 解决方案 > MySQL - custom, additional columns based on foreign keys with other table

mysql - MySQL - custom, additional columns based on foreign keys with other table

问题描述

I have the following database schema:

Table: products

| id | name   | content |
|----|--------|---------|
| 1  | Pen    | ...     |
| 2  | Pencil | ...     |
| 3  | Rubber | ...     |
| 4  | Ruler  | ...     |

Table: feature_types

| id | name     |
|----|----------|
| 1  | Color    |
| 2  | Material |
| 3  | ...      |
| 4  | ...      |

Table: features

| id | product_id | feature_type_id | value     |
|----|------------|-----------------|-----------|
| 1  | 1          | 1               | Red       |
| 2  | 1          | 2               | Aluminum  |
| 3  | 2          | 1               | Green     |
| 4  | 2          | 2               | Wood      |
| 5  | 3          | 1               | White     |
| 6  | 4          | 2               | Plastic   |

My question is how can I do something like this:

SELECT *, ... FROM products ...

With result:

| id | name   | content | feature_type_1 | feature_type_2 |
|----|--------|---------|----------------|----------------|
| 1  | Pen    | ...     | Red            | Aluminum       |
| 2  | Pencil | ...     | Green          | Wood           |
| 3  | Rubber | ...     | White          | NULL           |
| 4  | Ruler  | ...     | NULL           | Plastic        |

So as you see, in results we have all columns from products table and additional columns for specified feature_types. Column names correspond to their identifiers, according to the pattern: "feature_type_{ID}".

I know feature_types IDs so it is not necessary to add all possible columns feature_types. I need only 2 additional columns with ID 1 and 2.

标签： mysqlsql

解决方案

如果您只对以下功能感兴趣：Color和Material，请加入表格并按产品分组：

select
  p.id, p.name, p.content,
  max(case when t.name = 'Color' then f.value end) Color,
  max(case when t.name = 'Material' then f.value end) Material
from products p 
left join features f on f.product_id = p.id
left join feature_types t 
on t.id = f.feature_type_id and t.name in ('Color', 'Material')
group by p.id, p.name, p.content

我猜在您的示例数据中，您通过设置1而不是表中的2as feature_type_idfor犯了一个错误。 请参阅演示。 结果：Plasticfeatures

| id  | name   | content | Color | Material  |
| --- | ------ | ------- | ----- | --------- |
| 1   | Pen    | ...     | Red   | Aluminium |
| 2   | Pencil | ...     | Green | Wood      |
| 3   | Rubber | ...     | White |           |
| 4   | Ruler  | ...     |       | Plastic   |

推荐阅读