从 SQL 中的 LEFT JOIN 创建矩阵/表

Question

我想知道将 LEFT JOIN 转换为表/矩阵的好解决方案。

例如，给定这个模式：

objects
   id (integer)
   name (string)

attributes
   id (integer)
   object_id (integer)
   name (string)
   value (string)

和这些值：

1,ball
2,box

1,1,colour,red
2,1,shape,sphere
3,1,material,rubber
4,2,colour,brown
5,2,shape,cube

我想得到这个：

object_id | object_name | colour | shape  | material

1         | ball        | red    | sphere | rubber
2         | box         | brown  | cube   | NULL

理想情况下，每个对象都有未知数量的属性，并且适用于 MySQL，而不使用存储过程。

Answer 1

RDBMS 的特点是它们的目的是存储和呈现您已有的内容。 动态创建列从来都不是有意的。

这种情况应该在要呈现数据的客户端上处理。

不管 RDBMS 应该如何使用，您仍然找不到一种简单的方法来做到这一点。 尤其是如果您希望列列表是动态的。 您可以构建动态 SQL 查询并执行它，但无法编写标准 SQL 查询并获取结果（因为您总是显式指定所有列，禁止使用 *，但这不能在这种情况下可以利用你的优势。）

Answer 2

如果不使用 SQL 中的动态查询，则无法做到这一点。

SQL 处理的任何集合都假定具有固定数量的列和固定名称。

否则会导致许多 SQL 无法处理的事情。

就像，想象一下根据您要求的查询创建的视图（是否可能）：

SELECT  object_id, colour
FROM    myquery

该视图是否编译将取决于查询的结果，这会给您带来运行时错误（很难处理），而不仅仅是空的结果集（很容易处理）。

您在这里基本上谈论的是结果呈现，而这些事情通常是在客户端完成的。

如果您有包含所有可能的属性名称的 attribute_names 表，您可以执行以下操作：

SELECT  o.name, an.name, a.value
FROM    objects o
CROSS JOIN
        attribute_names an
LEFT JOIN
        attributes a
ON      a.object = o.id
        AND a.name = an.name

，它将包含所有可能的 object_id / attribute_name 对以及相应的值，并且您可以在客户端使用它来更轻松地填充矩阵。

Answer 3

假设结果表被称为合并，这将解决您的问题，尽管我认为您拥有的模式更好。

insert into merged (object_id, object_name) select id, name from objects;
update merged m inner join attributes a on a.object_id = m.object_id and a.name = 'colour' set m.colour = a.value;
update merged m inner join attributes a on a.object_id = m.object_id and a.name = 'material' set m.material = a.value;
update merged m inner join attributes a on a.object_id = m.object_id and a.name = 'shape' set m.shape = a.value;

Answer 4

嗯，这可能是您缺少 2 个逻辑步骤来实现这一点。

您需要的是 AttributeTypes 表和 AttributeValues 表。

objects
   id (integer)
   name (string)

attribute_types
   id (integer)
   name (string)
   description (string)
   enabled (bit)

attribute_values
   id (integer)
   attribute_type_id (integer)
   value (string)
   enabled (bit)

attributes
   id (integer)
   object_id (integer)
   attribute_type_id (integer)
   attribute_value_id (integer)

所以一个对象可以有属性，这些属性由attribute_type_id和attribute_value_id指定。

然后，这允许您拥有一个具有多个属性的对象。

例如

object 
-> 1, ball

attribute_types
-> 10, Colour, null, enabled
-> 20, Shape, null, enabled
-> 30, Material, null, enabled

attribute_values 
-> 100, 10, blue, enabled
-> 200, 10, red, enabled
-> 300, 10, green, enabled

-> 400, 20, round, enabled
-> 500, 20, square, enabled
-> 600, 20, triangle, enabled

，一个属性看起来像：

attributes
-> 1000, 1, 10, 100 // this item has a color and it is blue
-> 1001, 1, 20, 400 // this item has a shape and it is round
-> 1002, 1, 10, 200 // this item has a color and it is red

所以现在对象可以有多个属性，这些属性在不同的表中指定它们的值。 现在重要的问题是如何查询这个？ 您可能必须将查询分成多个部分，具体取决于您的 SQL 的强度。

@attribute_type_id = select Id from attribute_types where name = 'Color' // 10

select * from objects 
inner join attributes on objects.id = attributes.object_id
inner join attribute_values on objects.attribute_value_id = attribute_values.id
where attribute_type_id = @attribute_type_id
and attribute_values.value= 'blue'

现在您应该带回具有 Color 的 attribute_type 和蓝色的 attribute_value 的每个对象。

我的 Sql 不是那么强大，但如果您想一次搜索多个属性，您应该能够执行多个子句。 在我看来，属性表中的 attribute_type_id 没有加入到 attribute_type 表中，但它可以促进在 1 次命中中执行查询，尽管我拥有它，但性能方面，它可以通过不必加入 a 来加速查询表，但差异可能可以忽略不计，具体取决于一切的大小。

注意：我通常使用 msssql 而不是 mysql，所以如果数据库类型不匹配，这就是原因。