sql左联接并在结果中重复 [英] sql left join and duplicates in result

问题描述

说我有2个表，A和B，每个A实体都可以有多个B实体，在一种情况下，如果我想获得某些A的所有B，则可以通过简单的左连接来实现

Say I have 2 tables, A and B, each A entity can possibly have multiple B entities, in one case if I want to get all B's of some certain A's, I might do it with a simple left join

select A.id aid,B.id bid from A
left join B on B.aid = A.id
where A.id = 1

，它将返回类似

aid   bid
1     1
1     2
1     3

正如您在第一列中看到的那样，所有这些1都是重复的.是否可以修改SQL语句以使他返回类似的结果

As you can see for the first column, all those 1's are kinda duplicates. Is it possible to modify the SQL statement to let him return a result like

aid    bid
 1      1,2,3

换句话说，将所有出价作为一个实体链接在一起?

in other words to link all the bid's together as one entity?

如果还有另一个表C，并且每个A可以有多个C，该怎么办?如何使SQL返回类似这样的结果集?

Also what if there's another table C, and each A can have multiple C's, how to I make the SQL return a result set like

 aid    bid    cid
  1     1,2,3   1,2

代替

 aid     bid    cid
  1       1      1
  1       2      1
  1       3      1
  1       1      2
  1       2      2
  1       3      2 

非常感谢！

推荐答案

您正在使用什么DBMS?

What DBMS are you using?

我不能代表别人，但是在MySQL中，从4.1开始，您可以使用GROUP_CONCAT

I can't speak for others, but in MySQL, starting from 4.1, you can use GROUP_CONCAT

http://dev .mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

EG:

select A.id aid, GROUP_CONCAT(DISTINCT B.id) bid from A
left join B on B.aid = A.id
where A.id = 1
GROUP BY a.id

这篇关于sql左联接并在结果中重复的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！