问题描述
我有一个数据库设计，可以保存有关房屋的数据.有2个(相关的)表-一个保存属性代码，名称，描述等，第二个保存有关属性属性的信息.
I have a database design that holds data about houses. There are 2 (relevant) tables - one holds property code, name, description etc, and the second holds information about attributes of the property.
当前，我在属性表中有一些列(MaxDoubles，MaxSingles等)，这些列包含非规范化数据，我现在需要(出于各种原因)将这些数据保存在属性表中.实际上，我是将属性"表中的一系列列交换为属性"表中的一系列行.所以我现有的查询
Currently I have got columns in the property table (MaxDoubles, MaxSingles etc) that hold denormalised data that I need to now hold (for various reasons) in the attributes table. In effect I am swapping a series of columns in the Properties table for a series of rows in the Attributes table. So my existing query
SELECT MaxDoubles，MaxSinglesFROM属性
SELECT MaxDoubles, MaxSinglesFROM Properties
需要重新编写，以在加入属性时也为每个属性产生一行.如果我尝试
that returns one row per property needs re-writing to also produce one row per property when joined to the Attributes. If I try
选择A.MaxDoubles，A.MaxSinglesFROM属性P，属性A
SELECT A.MaxDoubles, A.MaxSinglesFROM Properties P, Attributes A
然后，每个属性我显然会返回多行.
then I obviously get multiple rows returned per property.
是否存在一种巧妙的方法来联接这些表，以便查询结果返回单行?
Is there a clever way of joining these tables so the query result returns a single row?
谢谢
推荐答案
假设像@Konerak的示例那样的架构，如果要包含属性及其所有属性的一行，则需要透视"属性记录.
Assuming a schema like @Konerak's example, you'll need to "pivot" the attributes records if you want one row containing a property and all of its attributes.
幸运的是，关于此操作的信息不乏. :)
这篇关于从查询联接多行表中返回一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！