mysql GROUP_CONCAT [英] mysql GROUP_CONCAT
本文介绍了mysql GROUP_CONCAT的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我想列出所有具有相应用户类别的用户. 这是我的桌子的简化版本
I want to list all users with their corropsonding user class. Here are simplified versions of my tables
CREATE TABLE users (
user_id INT NOT NULL AUTO_INCREMENT,
user_class VARCHAR(100),
PRIMARY KEY (user_id)
);
INSERT INTO users VALUES
(1, '1'),
(2, '2'),
(3, '1,2');
CREATE TABLE classes (
class_id INT NOT NULL AUTO_INCREMENT,
class_name VARCHAR(100),
PRIMARY KEY (class_id)
);
INSERT INTO classes VALUES
(1, 'Class 1'),
(2, 'Class 2');
这是我要使用的查询语句,但仅返回第一个匹配的用户类,而不是希望的连接列表.
And this is the query statement I am trying to use but is only returning the first matching user class and not a concatenated list as hoped.
SELECT user_id, GROUP_CONCAT(DISTINCT class_name SEPARATOR ",") AS class_name
FROM users, classes
WHERE user_class IN (class_id)
GROUP BY user_id;
实际输出
+---------+------------+
| user_id | class_name |
+---------+------------+
| 1 | Class 1 |
| 2 | Class 2 |
| 3 | Class 1 |
+---------+------------+
想要的输出
+---------+---------------------+
| user_id | class_name |
+---------+---------------------+
| 1 | Class 1 |
| 2 | Class 2 |
| 3 | Class 1, Class 2 |
+---------+---------------------+
预先感谢
推荐答案
这不是最佳设计,但这是您想要的查询:
This is not the best design, but here's the query you want:
SELECT user_id, GROUP_CONCAT(class_name)
FROM users
JOIN classes
ON FIND_IN_SET(class_id, user_class)
GROUP BY
user_id
这篇关于mysql GROUP_CONCAT的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文