将多个表的结果连接成1行 [英] Join results from multiple tables into 1 row

问题描述

希望你可以帮忙解决这个问题，因为它让我停留了很长一段时间。


我有两张桌子：


用户表

-----------------

id（主键）

firstname

lastname


主题表

---------------- -

id

fkUser

主题


个人资料表中的每个条目都可以包含多个条目在主题表中。 ''fkUser''字段链接到配置文件表''id''字段。


如果我运行以下查询，并且用户在主题中有多个条目表，遍历结果重复用户详细信息的次数与主题表中的条目一样多。因此，如果用户''John Smith''在主题表中有4个条目，则他的名字将被打印4次。


选择user.firstname，user.lastname，topics.topic

来自用户

LEFT JOIN主题ON topics.fkUser = user.id


问题是，如何返回结果集这给了我每个用户1行，包含他们有条目的所有主题。每个结果行看起来像：


John |史密斯| 3 | 9 | 2 | 4

大卫|白色| 2 | 5

彼得|布朗| 1 |


希望有人可以提供帮助，非常感谢。


P

解决方案

< blockquote>在上面的示例输出中，这些列的标题是什么（即第2,3列中的数字是什么......）？

感谢您的回复！数字代表主题字段中的条目。


这是用户表的一个例子：


id |名字|姓氏

1 |约翰|史密斯

2 |大卫|白色

3 |彼得|布朗


根据我指定的数字，这是''主题'表的外观：


id | fkUser |话题

1 | 1 | 3

2 | 1 | 9

3 | 1 | 2

4 | 1 | 4

5 | 2 | 2

6 | 2 | 5

7 | 3 | 1

感谢您的回复！数字代表主题字段中的条目。


这是用户表的一个例子：


id |名字|姓氏

1 |约翰|史密斯

2 |大卫|白色

3 |彼得|布朗


根据我指定的数字，这是''主题'表的外观：


id | fkUser |话题

1 | 1 | 3

2 | 1 | 9

3 | 1 | 2

4 | 1 | 4

5 | 2 | 2

6 | 2 | 5

7 | 3 | 1

所以你想要将行作为列返回？我不认为这是可能的。

例如返回的列数是多少？

Hi, hope you can help with this query as it''s had me stuck for quite a while.

I have two tables:

user table
-----------------
id (primary key)
firstname
lastname

topics table
-----------------
id
fkUser
topic

Each entry in the profile table can have multiple entries in the topics table. The ''fkUser'' field is linked to the profile table ''id'' field.

If I run the following query, and the user has more than 1 entry in the topics table, looping through the results repeats the user details as many times as there are entries in the topics table. So if user ''John Smith'' has 4 entries in the topics table, his name is printed 4 times.

Select user.firstname, user.lastname, topics.topic
FROM user
LEFT JOIN topics ON topics.fkUser = user.id

The question is, how could I return a result set which gives me 1 row per user, containing all of the topics they have entries for. Each results row would look like:

John | Smith | 3 | 9 | 2 | 4
David | White | 2 | 5
Peter | Brown | 1 |

Hope someone can help, many thanks.

P

解决方案

In your example output above, what are the headings for those columns (i.e what are those numbers in columns 2, 3, ... )?

Thanks for your response! The numbers represent entries in the ''topic'' field.

Here''s an example of the user table:

id | firstname | lastname
1 | John | Smith
2 | David | White
3 | Peter | Brown

This is how the ''topics'' table would look based on the numbers I specified:

id | fkUser | topic
1 | 1 | 3
2 | 1 | 9
3 | 1 | 2
4 | 1 | 4
5 | 2 | 2
6 | 2 | 5
7 | 3 | 1

Thanks for your response! The numbers represent entries in the ''topic'' field.

Here''s an example of the user table:

id | firstname | lastname
1 | John | Smith
2 | David | White
3 | Peter | Brown

This is how the ''topics'' table would look based on the numbers I specified:

id | fkUser | topic
1 | 1 | 3
2 | 1 | 9
3 | 1 | 2
4 | 1 | 4
5 | 2 | 2
6 | 2 | 5
7 | 3 | 1

So you want to return rows as columns? I don''t think that''s possible.
e.g What would be the number of columns returned?

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