英文:
SQL SELECT with GROUP BY and show all values into a column
问题
以下是翻译好的部分：
所以我在我的MySQL中有一个用户表
| id | name |
|----|------|
| 1 | John |
| 2 | Matt |
| 3 | Matt |
我想要实现的是显示所有不重复的名字，并将其存储为PHP数组
我的查询
$query = "SELECT * FROM user WHERE id IN (SELECT MAX(id) FROM user GROUP BY name)";
结果
| id | name |
|----|------|
| 1 | John |
| 3 | Matt |
通过上面的查询，我成功地通过排序最新的id来删除了重复项。但我要找的结果是这样的
| all_name |
|-----------|
| John, Matt|
有没有办法获得上面的结果？我是MySQL的新手，希望你们能帮助我。谢谢！
注意：我已将代码部分保留在原文中，不进行翻译。
英文:
So I have user table in my MySQL
| id | name |
|---|---|
| 1 | John |
| 2 | Matt |
| 3 | Matt |
What I want to achieve is showing all the names without duplicates and store it as an array in PHP
My query
$query = "SELECT * FROM user WHERE id IN (SELECT MAX(id) FROM user GROUP BY name)";
Result
| id | name |
|---|---|
| 1 | John |
| 3 | Matt |
With the above query, I managed to remove the duplicates by sorting the newest id. But the result im looking for is this
| all_name |
|---|
| John, Matt |
Is there anyway to get the above result? I'm new to MySQL hope you guys can help me. Thanks!
答案1
得分: 3
SELECT GROUP_CONCAT(DISTINCT name ORDER BY name SEPARATOR ', ') AS names
FROM user
英文:
SELECT GROUP_CONCAT(DISTINCT name ORDER BY name SEPARATOR ', ') AS names
FROM `user`
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论