INSERT INTO ""("id", "user_name", "real_name", "email", "org_id", "last_login_time", "created_time", "valid_period", "always", "name", "lock_status", "latest_failed_time", "wrong_pwd_time") VALUES (575526910805672420, '13330291922', '袁和姣', 'nancy.yy@foxmail.com', 454544777304604693, '2023-05-17 15:25:19', '2023-05-08 11:38:28.304166', '2023-05-08 11:38:28.304166', 't', '漏扫专员', 0, NULL, 0); INSERT INTO ""("id", "user_name", "real_name", "email", "org_id", "last_login_time", "created_time", "valid_period", "always", "name", "lock_status", "latest_failed_time", "wrong_pwd_time") VALUES (575526910805672420, '13330291922', '袁和姣', 'nancy.yy@foxmail.com', 454544777304604693, '2023-05-17 15:25:19', '2023-05-08 11:38:28.304166', '2023-05-08 11:38:28.304166', 't', '成员', 0, NULL, 0); INSERT INTO ""("id", "user_name", "real_name", "email", "org_id", "last_login_time", "created_time", "valid_period", "always", "name", "lock_status", "latest_failed_time", "wrong_pwd_time") VALUES (575526910805672420, '13330291922', '袁和姣', 'nancy.yy@foxmail.com', 454544777304604693, '2023-05-17 15:25:19', '2023-05-08 11:38:28.304166', '2023-05-08 11:38:28.304166', 't', '单位接口人', 0, NULL, 0); 这三条语句是数据库temp中数据, 用sql查询数据库的数据,查询结果要求是这样的额 (575526910805672420, '13330291922', '袁和姣', 'nancy.yy@foxmail.com', 454544777304604693, '2023-05-17 15:25:19', '2023-05-08 11:38:28.304166', '2023-05-08 11:38:28.304166', 't', '漏扫专员,成员,单位接口人', 0, NULL, 0);
时间: 2023-09-08 20:08:03 浏览: 153
你需要使用 GROUP BY 和 GROUP_CONCAT 函数来将同一用户的职位名合并为一个字段,查询语句如下:
```
SELECT id, user_name, real_name, email, org_id, last_login_time, created_time, valid_period, always, GROUP_CONCAT(name SEPARATOR ',') AS name, lock_status, latest_failed_time, wrong_pwd_time
FROM temp
GROUP BY id, user_name, real_name, email, org_id, last_login_time, created_time, valid_period, always, lock_status, latest_failed_time, wrong_pwd_time;
```
查询结果将会是:
```
id | user_name | real_name | email | org_id | last_login_time | created_time | valid_period | always | name | lock_status | latest_failed_time | wrong_pwd_time
-------------------|-------------|-----------|-----------------------|----------------|--------------------------|--------------------------|-----------------------|--------|---------------------------------------------|--------------|-----------------------|----------------
575526910805672420 | 13330291922 | 袁和姣 | nancy.yy@foxmail.com | 454544777304604693 | 2023-05-17 15:25:19 | 2023-05-08 11:38:28.304166 | 2023-05-08 11:38:28.304166 | t | 漏扫专员,成员,单位接口人 | 0 | NULL | 0
```
阅读全文