mysql - 我需要停止重复 Mysql 多对多关系中的行

Question

我有两个sql表1) 员工 {id, first_name, last_name, job_title, salary}2) 教育程度 {id, name}

一旦我尝试通过此查询从这两个表中获取数据，我就会得到像这样的重复行

first name  last name   qualification
Robin        Jackman    BSc
Taylor        Edward    BSc
Vivian       Dickens    MSc
Vivian       Dickens    PhD

$query = "SELECT emp.first_name, emp.last_name, edu.name AS edu_name 
          FROM `employee` AS emp 
          LEFT JOIN `employee_education` AS ee ON emp.id = ee.employee_id 
          LEFT JOIN `education` AS edu ON ee.education_id = edu.id";

但是我期望的结果是这样的

first name  last name   qualification
Robin        Jackman    BSc
Taylor        Edward    BSc
Vivian       Dickens    MSc PhD

您知道如何完成此任务吗？谢谢。

表结构

CREATE TABLE `employee` (
`id` int NOT NULL AUTO_INCREMENT,
`first_name` varchar(100) NOT NULL,
`last_name` varchar(100) NOT NULL,
`job_title` varchar(100) DEFAULT NULL,
`salary` double DEFAULT NULL,
`notes` text,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `employee` (`first_name`, `last_name`, `job_title`, `salary`) VALUES 
('Robin', 'Jackman', 'Software Engineer', 5500),
('Taylor', 'Edward', 'Software Architect', 7200),
('Vivian', 'Dickens', 'Database Administrator', 6000),
('Harry', 'Clifford', 'Database Administrator', 6800),
('Eliza', 'Clifford', 'Software Engineer', 4750),
('Nancy', 'Newman', 'Software Engineer', 5100),
('Melinda', 'Clifford', 'Project Manager', 8500),
('Harley', 'Gilbert', 'Software Architect', 8000);


CREATE TABLE `education` (
`id` tinyint NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `education` (`name`) VALUES ('BSc'), ('MSc'), ('PhD');

Answer 1

使用GROUP_CONCAT组合名称:

SELECT emp.first_name, emp.last_name, GROUP_CONCAT(edu.name ORDER BY edu.name SEPARATOR ' ') AS edu_name 
FROM `employee` AS emp 
LEFT JOIN `employee_education` AS ee ON emp.id = ee.employee_id 
LEFT JOIN `education` AS edu ON ee.education_id = edu.id
GROUP BY emp.id