MySQL创建 View 时如何动态选择从哪个表获取数据

Question

我是一名学生，对 SQL 的经验很少。我正在尝试从多个不同的表收集数据并将相关信息收集到 MySQL 中的单个 View 中。问题是查询的结构对于我的技能来说太复杂了，老实说，我什至不确定是否可以按照我尝试的方式做到这一点。

这是我尝试在此处使用的表的基本结构(名称已更改):产品_产品_属性:产品_id、产品_属性_id、值

产品属性: ID、名称

Material : ID、名称

基本上与 Material 相同的结构，然后重复用于其他 4 个属性类型表，例如颜色表

因此，我需要创建一个查询，该查询将在 View 中显示 Product_id、属性名称及其值的说明。名称是根据 id 从 Product_attribute 表中获取的，但值的解释是根据给定的值和 Product_attribute_id 是什么从 5 个属性表之一中获取的。

例如，产品 1 具有属性 1，属性值为 3，因此首先我们通过 Product_attribute_id 确定有问题的属性(在本例中为 Material )，然后我们必须转到 Material 表看看值“3”意味着什么(假设它意味着钢)。然后对产品具有的每个属性和每个产品重复此操作。

但是我如何进行一个查询，能够根据product_attribute_id从5个不同的表中获取值的解释？

如果你想看的话，这是我在思考这个问题时所取得的进展，尽管它远不正确(而且它显然只是试图获得 Material 属性值的解释，因为我不知道如何开始获得所有其他属性) :

CREATE OR REPLACE VIEW allattributesview AS

SELECT t1.product_id, t2.name, (SELECT m.name
        FROM product_product_attribute t LEFT JOIN material m  ON t.value = m.id 
        WHERE t.value = m.id) 
FROM product_product_attribute t1 LEFT JOIN product_attribute t2 ON t1.product_attribute_id = t2.id
WHERE t1.product_attribute_id = t2.id

我认为我需要使用 CASE 子句，但我不知道将其放在哪里或如何格式化它。请帮忙。

编辑:

示例数据:

产品_产品_属性

product_id  product_attribute_id  value
1           1                     3
1           2                     4
1           4                     1
2           1                     2

产品属性:

id  name

1   Material
2   Color
3   Some
4   Other
5   Attributes

Material

id  name

1   Steel
2   Silver
3   Wood

颜色

id  name
1   Red
2   Blue
3   Green
4   Yellow

其他 3 个属性表重复相同的样式

我想要实现的结果:

product_id  name      value

1           Material  Wood
1           Color     Yellow

等等

我希望这有助于使我格式错误的问题更加清晰。

Answer 1

不相信您的设计，并且此解决方案“非规范化”以达到从第一个表进行简单连接即可输出正确结果的地步，我认为 - 请注意，我的结果集中比您多一个结果。

CREATE TABLE Product_product_attribute (product_id INT, product_attribute_id  INT, value INT);
INSERT INTO Product_product_attribute VALUES
(1  ,         1 ,                    3),
(1  ,         2 ,                    4),
(1  ,         4 ,                    1),
(2  ,         1 ,                    2);

CREATE TABLE Product_attribute(id INT, name VARCHAR(20));
INSERT INTO Product_attribute VALUES
(1  , 'Material'),
(2  , 'Color'),
(3  , 'Some'),
(4  , 'Other'),
(5  , 'Attributes');

CREATE TABLE Material (id INT, name VARCHAR(20));
INSERT INTO MATERIAL VALUES
(1  , 'Steel'),
(2  , 'Silver'),
(3  , 'Wood');

CREATE TABLE Color(id INT, name VARCHAR(20));
INSERT INTO COLOR VALUES
(1 ,  'Red'),
(2 ,  'Blue'),
(3 ,  'Green'),
(4 ,  'Yellow');

 select ppa.*,b.*
FROM Product_product_attribute PPA
join
(
select id, name ,a.*
from    Product_attribute PA 
join
(
select 'material' attribute, id attributeid, name attributevalue from material
union 
select 'color' attribute, id, name from color
) a
on a.attribute = pa.name
) b
on b.id = ppa.product_attribute_id AND b.attributeid = ppa.value
order by ppa.product_id, ppa.product_attribute_id,ppa.value

结果

+------------+----------------------+-------+------+----------+-----------+-------------+----------------+
| product_id | product_attribute_id | value | id   | name     | attribute | attributeid | attributevalue |
+------------+----------------------+-------+------+----------+-----------+-------------+----------------+
|          1 |                    1 |     3 |    1 | Material | material  |           3 | Wood           |
|          1 |                    2 |     4 |    2 | Color    | color     |           4 | Yellow         |
|          2 |                    1 |     2 |    1 | Material | material  |           2 | Silver         |
+------------+----------------------+-------+------+----------+-----------+-------------+----------------+
3 rows in set (0.00 sec)