我的sql从多个表中获取数据，逗号分隔

Question

>我有 3 个表格如下医生

CREATE TABLE `doctors` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `last_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `phone_number` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `email` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `avatar_path` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `qualification` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `status` tinyint(4) NOT NULL DEFAULT '0',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `doctors_email_unique` (`email`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

专门化

CREATE TABLE `specializations` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `status` tinyint(4) NOT NULL DEFAULT '0',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=105 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

以及这两个表中的多对多

CREATE TABLE `doctor_specialization` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `doctor_id` int(10) unsigned NOT NULL,
  `specialization_id` int(10) unsigned NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `doctor_specialization_doctor_id_foreign` (`doctor_id`),
  KEY `doctor_specialization_specialization_id_foreign` (`specialization_id`),
  CONSTRAINT `doctor_specialization_doctor_id_foreign` FOREIGN KEY (`doctor_id`) REFERENCES `doctors` (`id`),
  CONSTRAINT `doctor_specialization_specialization_id_foreign` FOREIGN KEY (`specialization_id`) REFERENCES `specializations` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

现在，我希望查询从医生那里获得结果，如果超过 1，则用逗号分隔。

有没有办法以 mysql 方式实现这一目标？或者我将不得不对每个结果集执行php loops？

Answer 1

您可以使用MySQL内置函数 - GROUP_CONCAT

SELECT  d.first_name,
        d.last_name,
        GROUP_CONCAT(s.name) AS `specializations`
FROM    doctors d
        INNER JOIN doctor_specialization ds
            ON d.id = ds.doctor_id
        INNER JOIN specializations s
            ON ds.specialization_id = s.id
GROUP   BY d.first_name, d.last_name

Answer 2

-- 你应该使用类似 INNER JOIN 的东西

SELECT column_name(s)
FROM table1
INNER JOIN table2 ON table1.column_name = table2.column_name;

好的，现在我们开始使用以下语法：

SELECT *
FROM doctors
INNER JOIN doctor_specialization ON doctors.id = doctor_specialization.doctor_id;

您可以根据需要在语句上使用许多内部连接。

SELECT *
FROM doctors
INNER JOIN doctor_specialization ON doctors.id = doctor_specialization.doctor_id
INNER JOIN doctor_address ON doctors.id = doctor_address.doctor_id;