mysql - 使用数据透视表时如何选择?
问题描述
考虑以下SQLFiddle。
DROP TABLE IF EXISTS appointments;
CREATE TABLE appointments (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL
);
DROP TABLE IF EXISTS services;
CREATE TABLE services (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL
);
DROP TABLE IF EXISTS join_services_appointment;
CREATE TABLE join_services_appointment (
service_id INT(6) UNSIGNED,
appointment_id INT(6) UNSIGNED,
FOREIGN KEY(service_id) references services(id),
FOREIGN KEY(appointment_id) references appointments(id)
);
INSERT INTO appointments (name) VALUES ('appointment1');
INSERT INTO appointments (name) VALUES ('appointment2');
INSERT INTO appointments (name) VALUES ('appointment3');
INSERT INTO services (name) VALUES ('services1');
INSERT INTO services (name) VALUES ('services2');
INSERT INTO services (name) VALUES ('services3');
INSERT INTO services (name) VALUES ('services4');
INSERT INTO services (name) VALUES ('services5');
INSERT INTO join_services_appointment (service_id, appointment_id) VALUES (1, 1);
INSERT INTO join_services_appointment (service_id, appointment_id) VALUES (2, 1);
INSERT INTO join_services_appointment (service_id, appointment_id) VALUES (2, 2);
INSERT INTO join_services_appointment (service_id, appointment_id) VALUES (3, 2);
INSERT INTO join_services_appointment (service_id, appointment_id) VALUES (3, 3);
INSERT INTO join_services_appointment (service_id, appointment_id) VALUES (4, 3);
我将使用什么查询来获取以下结果?
appointment_name services
appointment1 services1,services2
appointment2 services2,services3
appointment3 services3,services4
解决方案
如评论中所述,您可以使用GROUP_CONCAT()来实现所需的输出。
SELECT
a.name AS appointment_name,
GROUP_CONCAT(DISTINCT s.name ORDER BY s.name SEPARATOR ', ') AS services
FROM appointments a
INNER JOIN join_services_appointment sa ON a.id = sa.appointment_id
INNER JOIN services s ON s.id = sa.service_id
GROUP BY a.id;
推荐阅读
- python-3.x - 如何进行对称加密?我有问题
- docker - tomcat docker挂载卷变空
- java - 片段 - 标记、条件和显示
- react-native - React Native 中如何实现滑动访问不同的导航界面?
- express - 如何从 NestJS 中的 GraphQL 解析器设置会话?
- azure - Azure Databricks 的 Azure DevOps 管道
- opencv - 无法在 Renesas R-Car H3 (H3ULCB) 上安装 libgtk2.0-dev_2.24.31-2_arm64.deb
- python - 在 Keras 的 Lambda 层内执行一次热编码以避免内存问题:问题
- c# - Unity:如何通过过渡调整切片精灵的大小?
- wordpress - 创建帖子类型的新帖子时发送电子邮件通知