mysql-一对多查询，一行显示（group

# 作用

### 关联查询

关联查询，李雷会显示多行记录，如下：

```
select  a.name,b.cname  from teacher a,classes b where a.id=b.t_id
```

[![](https://www.malaoshi.top/upload/pic/mysql/QQ20211009161053.png)](https://www.malaoshi.top/upload/pic/mysql/QQ20211009161053.png)

### group_concat() 查询

如果想只显示 `李雷` 一行记录，对应的班级由 `,` 分割，如下：

```
select  a.name,group_concat(b.cname) as classes  from teacher a,classes b where a.id=b.t_id  GROUP BY a.id
```

[![](https://www.malaoshi.top/upload/pic/mysql/QQ20211009161116.png)](https://www.malaoshi.top/upload/pic/mysql/QQ20211009161116.png)

可以看到 根据 `id` 分成了2行，并且 `classes` 默认用 `,` 分割 但是有每个id有重复数据 接下来去重

### 作用

`group_concat()` 搭配 `GROUP BY` 使用，对于上例来说，先根据 `a.id` 分组，然后将 `b.cname` 的值 **连接**（`concat`）起来，由 `,` 分割

# 建表

### classes

```
CREATE TABLE `classes` (
  `cid` char(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `cname` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `t_id` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`cid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
```

### teacher
```
CREATE TABLE `teacher` (
  `id` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `name` varchar(128) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
```

# 去重

如果 `classes` 有重复的，需要去重

```
select  a.name,group_concat( DISTINCT b.cname) as classes  from teacher a,classes b where a.id=b.t_id  GROUP BY a.id
```

# 排序

```
select  a.name,group_concat( b.cname  ORDER BY b.cname DESC) as classes  from teacher a,classes b where a.id=b.t_id  GROUP BY a.id
```

# 设置分隔符

```
select  a.name,group_concat( b.cname SEPARATOR ';') as classes  from teacher a,classes b where a.id=b.t_id  GROUP BY a.id
```

原文出处：/show_1IX20iDiIkMB.html