SQL左连接-相同的结果多次

I have the following tables:

我有以下表格:

users

用户

id | name

id |名称

info

信息

id | info | user_id

id | info | user_id

(user_id from table info is foreign key => connects to id from table users)

(user_id来自表info是外键=>连接到表用户的id)

It is possible that the user will have many entries in the info table (same user_id in many rows).

用户可能在info表中有许多条目(在许多行中有相同的user_id)。

Now I want to fetch data and display it like that:

现在我想取回数据，然后像这样显示:

username:

... info ....

Example:

例子:

admin:

aaa

bbb

ccc

someuser:

ddd

eee

fff

So I use LEFT JOIN like that:

我用左连接

SELECT users.name, info.info
FROM users
LEFT JOIN info
ON users.id = info.user_id

But what I get is the following:

但我得到的是:

admin:

aaa

admin:

bbb

admin:

ccc

How can i display the username only once? I've tried using DISTINCT keyword after SELECT but it didn't help. Meanwhile i solve this problem inside the php code itself, but is there any way to fix this only inside the sql?

如何只显示一次用户名?我试过在SELECT之后使用不同的关键字，但是没有用。同时，我在php代码本身中解决了这个问题，但是是否有办法只在sql中解决这个问题?

2 个解决方案

#1

SELECT users.name, GROUP_CONCAT(info.info SEPARATOR 'whateveryouwant') as info
FROM users 
INNER JOIN info ON (users.id = info.user_id)
GROUP BY users.name

By default group_concat uses a , as separator, but you can change that if needed.

默认情况下group_concat使用a作为分隔符，但如果需要，您可以更改它。

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

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

#2

$lastname="";
$res=mysql_query($yourQuery);
while($row=mysql_fetch_assoc($res)){
    if($row['name']!=$lastname){
        $lastname=$row['name'];
        print $lastname.':'; // first time print it
    }
    print $row['info'];
}

#1

SELECT users.name, GROUP_CONCAT(info.info SEPARATOR 'whateveryouwant') as info
FROM users 
INNER JOIN info ON (users.id = info.user_id)
GROUP BY users.name