MySQL 如何合并几行记录

为此，使用CASE WHEN概念。让我们首先创建一张表 –

mysql> create table demo68
−> (
−> id int not null auto_increment primary key,
−> company_name varchar(50),
−> employee_name varchar(50),
−> country_name varchar(50)
−> );
Query OK, 0 rows affected (1.86 sec)

使用insert命令将一些记录插入表中 –

mysql> insert into demo68(company_name,employee_name,country_name)
values('Google','John','US');
Query OK, 1 row affected (0.29 sec)

mysql> insert into demo68(company_name,employee_name,country_name)
values('Google','Bob','UK');
Query OK, 1 row affected (0.10 sec)

mysql> insert into demo68(company_name,employee_name,country_name)
values('Google','David','AUS');
Query OK, 1 row affected (0.08 sec)

使用select语句显示表中的记录 –

mysql> select *from demo68;

这将产生以下输出 –

+----+--------------+---------------+--------------+
| id | company_name | employee_name | country_name |
+----+--------------+---------------+--------------+
| 1  | Google       | John          | US           |
| 2  | Google       | Bob           | UK           |
| 3  | Google       | David         | AUS          |
+----+--------------+---------------+--------------+
3 rows in set (0.00 sec)

以下是在MySQL中合并几行记录的查询 –

mysql> select
−> company_name,
−> max(case when country_name= 'US' then employee_name end) as
US_Employee_Name,
−> max(case when country_name= 'UK' then employee_name end) as
UK_Employee_Name,
−> max(case when country_name= 'AUS' then employee_name end) as
AUS_Employee_Name
−> from demo68
−> group by company_name;

这将产生以下输出 –

+--------------+------------------+------------------+-------------------+
| company_name | US_Employee_Name | UK_Employee_Name | AUS_Employee_Name |
+--------------+------------------+------------------+-------------------+
| Google       | John             | Bob              | David             |
+--------------+------------------+------------------+-------------------+
1 row in set (0.05 sec)

阅读更多：MySQL 教程