从连接多行表的查询中返回单行

Question

我有一个数据库设计，用于保存有关房屋的数据。有 2 个（相关）表 - 第一个保存属性代码、名称、描述等，第二个保存有关属性属性的信息。

目前，我在属性表中有列（MaxDoubles、MaxSingles 等），这些列保存了我现在需要（出于各种原因）在属性表中保存的非规范化数据。实际上，我将“属性”表中的一系列列交换为“属性”表中的一系列行。所以我现有的查询

SELECT MaxDoubles, MaxSingles 每个属性返回一行的FROM 属性

需要重写，以便在连接到属性时也为每个属性生成一行。如果我尝试

SELECT A.MaxDoubles, A.MaxSingles FROM Properties P, Attributes A

然后我显然会得到每个属性返回的多行。

是否有一种巧妙的方法来连接这些表，以便查询结果返回一行？

谢谢

Answer 1

假设像 @Konerak 的示例这样的模式，如果您希望一行包含属性及其所有属性，则需要“旋转”属性记录。

幸运的是，不乏关于如何做到这一点的信息。 :)

Answer 2

您现在拥有的称为 EAV 数据结构 而你想做的就是“转向”。除了使用子选择之外，还有两种可能性。

使用 GROUP BY：

SELECT P.Property_ID,
       MAX(IF(A.Type = 'maxsingles',A.Value,0)) AS MaxSingles,
       MAX(IF(A.Type = 'maxdoubles',A.Value,0)) AS MaxDoubles
FROM Properties P
JOIN Attributes A USING (Property_ID)
GROUP BY Property_ID

对每个属性使用一个 JOIN：

SELECT P.Property_ID, A1.Value AS MaxSingles, A2.Value AS MaxDoubles
FROM Properties P
JOIN Attributes A1 ON (A1.Property_ID = P.Property_ID AND A1.Type = 'maxsingles')
JOIN Attributes A2 ON (A2.Property_ID = P.Property_ID AND A2.Type = 'maxdoubles')

Answer 3

我们需要您提供更多信息来处理您的问题。例如：

您的表是什么样的（SHOW CREATE TABLE Properties）
您想要执行什么查询？
你如何加入你的桌子？

此架构已标准化，足以允许典型的所有查询：

表属性：

• PropertyID
• PropertyName
• Propertydescription

表属性：

• AttributeID
• PropertyID
• AttributeName
• AttributeValue

因此，如果您拥有房产 1，则拥有 3 号房产的白宫卧室，你可以有

PropertyID: 1
PropertyName: Charming white landhouse with 3 spacious bedrooms
Propertydescription: This charming landhouse will ...

Attributes
AttributeID: 1
PropertyID: 1
AttributeName: NrBedrooms
AttributeValue: 3

AttributeID: 2
PropertyID: 1
AttributeName: NrBathrooms
AttributeValue: 2

AttributeID: 3
PropertyID: 1
AttributeName: Kitchen
AttributeValue: Fully Equiped

现在如果你想知道你的房子有多少间卧室，你可以问

SELECT A.AttributeValue from Attributes A
INNER JOIN Properties P
ON P.PropertyID = A.PropertyID
WHERE P.PropertyID = 1
and A.AttributeName = 'NrBedrooms'