从数据库中获取唯一记录，所有id以逗号分隔

Question

我有一个表，并希望找到重复的名称记录与逗号分隔的id

table users
Id     name
1      xyz
2      abc
3      def
4      xyz
5      sss
6      def

我想要用户唯一记录的id与逗号分隔的id，在我的例子中，我想要1,4用户'xyz'和3,6用户'def'

我已经尝试了唯一的查询，但它只返回最后或第一个id

寻找mysql查询或laravel eloquent的帮助。

Answer 1

// get the ids of users with the name `xyz` and join them together
User::where('name', 'xyz')->pluck('id')->join(',')

如果你想为所有用户设置:

$data = [];
User::pluck('name')->each(function ($user) {
$data[] = [
'name' => $user;
'matches' => User::where('name', $user)->pluck('id')->join(',')
];
});

你会得到一个数组，像:

[['name' => 'xyz', 'matches' => '1,4'], [['name' => 'abc', 'matches' => '2'], [...]]

Answer 2

试试:

$values = [];
$user = User::where('name', 'xyz');
$users = DB::table('users')->select('*')->where('name',$user->name)->get();
foreach ($users as $user) {
foreach(explode(',', $users ->id) as $value) {
$values[] = trim($value);
}
}
$values = array_unique($values);

Answer 3

您可以在SQL中使用:

select name, group_concat(id) as id
from t
group by name

如果您只想要包含多个id的名称，则添加:

having count(*) > 1