将一行连接到另一个表中的多个行 [英] Join one row to multiple rows in another table

问题描述

我有一个表给实体（让他们称为人）和属性（一个人可以有任意数量的属性）。例如：

I have a table to entities (lets call them people) and properties (one person can have an arbitrary number of properties). Ex:

Name  Age
--------
Jane  27
Joe   36
Jim   16

属性

Properties

Name   Property
-----------------
Jane   Smart
Jane   Funny
Jane   Good-looking
Joe    Smart
Joe    Workaholic
Jim    Funny
Jim    Young

我想编写一个高效的选择，根据年龄选择人，并返回所有或部分属性。 p>

I would like to write an efficient select that would select people based on age and return all or some of their properties.

Ex: People older than 26
Name Properties
Jane Smart, Funny, Good-looking
Joe Smart, Workaholic

也可以返回其中一个属性和总属性数。

It's also acceptable to return one of the properties and total property count.

查询应该是高效的：人表中有数百万行，属性表中有数十万行（因此大多数人没有属性）。

The query should be efficient: there are millions of rows in people table, hundreds of thousands of rows in properties table (so most people have no properties). There are hundreds of rows selected at a time.

有任何方法吗？

推荐答案

使用：

   SELECT x.name,
          GROUP_CONCAT(y.property SEPARATOR ', ')
     FROM PEOPLE x
LEFT JOIN PROPERTIES y ON y.name = x.name
    WHERE x.age > 26
 GROUP BY x.name

您希望MySQL函数GROUP_CONCAT（ documentation ），以便返回逗号分隔

You want the MySQL function GROUP_CONCAT (documentation) in order to return a comma separated list of the PROPERTIES.property value.

我使用LEFT JOIN而不是JOIN来包含在PROPERTIES表中没有值的PEOPLE记录。如果只想要在PROPERTIES表中具有值的人员列表，请使用：

I used a LEFT JOIN rather than a JOIN in order to include PEOPLE records that don't have a value in the PROPERTIES table - if you only want a list of people with values in the PROPERTIES table, use:

   SELECT x.name,
          GROUP_CONCAT(y.property SEPARATOR ', ')
     FROM PEOPLE x
     JOIN PROPERTIES y ON y.name = x.name
    WHERE x.age > 26
 GROUP BY x.name

我意识到这是一个例子，是参考完整性的一个不好的选择，当你考虑有多少约翰·史密斯。分配user_id是一个数值，将是一个更好的选择。

I realize this is an example, but using a name is a poor choice for referencial integrity when you consider how many "John Smith"s there are. Assigning a user_id, being a numeric value, would be a better choice.

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