MySql采用GROUP_CONCAT合并多条数据显示的方法
Jun 07, 2016 pm 04:27 PM本文实例讲述了MySql采用GROUP_CONCAT合并多条数据显示的方法,分享给大家供大家参考。具体实现方法如下: 假设有这样一个需求: 1:班级表: id class_name 2:学生表: id name class_id 如果我们要查所有的班级以及每个班级下的学生,你一定会想到这样写法:
本文实例讲述了MySql采用GROUP_CONCAT合并多条数据显示的方法,分享给大家供大家参考。具体实现方法如下:
假设有这样一个需求:
1:班级表:
id class_name
2:学生表:
id name class_id
如果我们要查所有的班级以及每个班级下的学生,你一定会想到这样写法:
SELECT
banji.*, user.name
FROM `banji`
left join user
on banji.id=user.`class`
where banji.id=2
这样我们得到的结果的个数是和学生人数相同的,而我希望得到的就是一条数据,而这条数据中还要包含所有学生,可以这样写:
SELECT
banji.*, GROUP_CONCAT(user.name) as names
FROM `banji`
left join user
on banji.id=user.`class`
where banji.id=2
这样就只得到一条结果了,而结果中包含所有的学生name,
id | class_name | names |
2 | 二 | lisi,zhaoliu,liu,小二,xiaoming,小张 |
还可以自定义分隔符:
SELECT
banji.*, GROUP_CONCAT(user.name SEPARATOR "|") as names
FROM `banji`
left join user
on banji.id=user.`class`
where banji.id=2
得到的结果:
id | class_name | names |
2 | 二 | lisi|zhaoliu|liu|小二|xiaoming|小张 |
二 希望本文所述对大家的MySQL数据库程序设计有所帮助。

Hot Article

Hot tools Tags

Hot Article

Hot Article Tags

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics

PHP's big data structure processing skills

The U.S. Air Force showcases its first AI fighter jet with high profile! The minister personally conducted the test drive without interfering during the whole process, and 100,000 lines of code were tested for 21 times.

How to optimize MySQL query performance in PHP?

Tesla robots work in factories, Musk: The degree of freedom of hands will reach 22 this year!

How to use MySQL backup and restore in PHP?

How to insert data into a MySQL table using PHP?

How to fix mysql_native_password not loaded errors on MySQL 8.4

How to use MySQL stored procedures in PHP?
