将一行连接到另一个表中的多个行

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

我有一个实体表(我们称它们为人员)和属性(一个人可以有任意数量的属性)。例:

People

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

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.

查询应该是有效的:在人员表中有数百万行，在属性表中有数十万行(因此大多数人没有属性)。一次选择了数百行。

Is there any way to do it?

有什么办法吗?

3 个解决方案

#1

Use:

使用:

   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

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

您希望MySQL函数GROUP_CONCAT(文档)返回一个逗号分隔的属性列表。属性值。

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

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.

我知道这是一个例子，但是当你考虑有多少个“约翰·史密斯”的时候，用一个名字来指代诚信是个糟糕的选择。为user_id分配一个数字值是更好的选择。

#2

You can use INNER JOIN to link the two tables together. More info on JOINs.

可以使用内部连接将两个表链接在一起。在加入更多信息。

SELECT *
FROM People P
INNER JOIN Properties Pr
  ON Pr.Name = P.Name
WHERE P.Name = 'Joe' -- or a specific age, etc

However, it's often a lot faster to add a unique primary key to tables like these, and to create an index to increase speed.

但是，向这些表添加惟一的主键并创建索引以提高速度通常要快得多。

Say the table People has a field id
And the table Properties has a field peopleId to link them together

假设表人员有字段id，表属性有字段peopleId将它们链接在一起

Then the query would then look something like this:

然后查询就会变成这样:

SELECT *
FROM People P
INNER JOIN Properties Pr
  ON Pr.id = P.peopleId
WHERE P.Name = 'Joe'

#3

SELECT x.name,(select GROUP_CONCAT(y.Properties SEPARATOR ', ')
FROM PROPERTIES y 
WHERE y.name.=x.name ) as Properties FROM mst_People x

try this

试试这个

#1