从查询联接多行表中返回一行 [英] Returning a single row from query joining multi-row tables

问题描述

我有一个数据库设计，可以保存有关房屋的数据.有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，MaxSingles FROM属性

SELECT MaxDoubles, MaxSingles FROM 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.MaxSingles FROM属性P，属性A

SELECT A.MaxDoubles, A.MaxSingles FROM 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.

幸运的是，关于此操作的信息不乏. :)

这篇关于从查询联接多行表中返回一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！