MySQL 将一行连接到另一个表中的多行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2892816/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Join one row to multiple rows in another table
提问by Ghostrider
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.
查询应该是高效的：people 表中有数百万行，properties 表中有数十万行（所以大多数人没有属性）。一次选择数百行。
Is there any way to do it?
有什么办法吗？
回答by OMG Ponies
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（文档）以返回 PROPERTIES.property 值的逗号分隔列表。
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:
为了包含在 PROPERTIES 表中没有值的 PEOPLE 记录，我使用了 LEFT JOIN 而不是 JOIN - 如果您只想要在 PROPERTIES 表中具有值的人员列表，请使用：
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，作为一个数值，将是一个更好的选择。
回答by mayur awati
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
尝试这个
回答by Alec
You can use INNER JOINto link the two tables together. More info on JOINs.
您可以使用INNER JOIN将两个表链接在一起。有关 JOIN 的更多信息。
SELECT *
FROM People P
INNER JOIN Properties Pr
ON Pr.Name = P.Name
WHERE P.Name = 'Joe' -- or a specific age, etcHowever, it's often a lot faster to add a unique primary keyto tables like these, and to create an indexto increase speed.
但是，向此类表添加唯一主键并创建索引以提高速度通常要快得多。
Say the table Peoplehas a field id
And the table Propertieshas a field peopleIdto link them together
假设该表People有一个字段id
并且该表Properties有一个字段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'