时间戳

首页 > 解决方案 > 使用条件mysql合并多个数据

mysql - 使用条件mysql合并多个数据

问题描述

我有一个这样的数据库

ID | booking_id | branch_id | service_id | staff_id | rating
1  |    21      |    2      |   null     |   null   |   5
2  |    21      |    null   |    5       |   null   |   3
3  |    21      |    null   |    null    |    7     |   5
4  |    22      |    3      |   null     |   null   |   4
5  |    22      |    null   |    8       |   null   |   2
6  |    22      |    null   |    null    |    10    |   1

有没有一种方法可以提取合并具有相同 booking_id 的数据，结果将是这样的

booking_id |branch name  |service name| staff name |branch rating|service rating|staff rating 
    21     |LA branch    |   massage  |   John     |  5          |      3       | 5
    22     |Vegas Branch |   therapy  |   May      |  4          |      2       | 1

标签： mysqlsql

解决方案

您可以使用MAX()函数来获取null给定的非值booking_id。像这样的东西：

SELECT booking_id,
       MAX(branch_id)                                AS branch_id,
       MAX(service_id)                               AS service_id,
       MAX(staff_id)                                 AS staff_id,
       MAX(IF(branch_id IS NOT NULL, rating, NULL))  AS branch_rating,
       MAX(IF(service_id IS NOT NULL, rating, NULL)) AS service_rating,
       MAX(IF(staff_id IS NOT NULL, rating, NULL))   AS staff_rating
FROM booking_rating # assuming the table name
GROUP BY booking_id;

现在您已经更新了您的问题，它使我之前的答案无效。这是更新后的查询的样子：

SELECT booking_id,
       branch.name  AS branch_name,
       service.name AS service_name,
       staff.name   AS staff_name,
       branch_rating,
       service_rating,
       staff_rating
FROM (
         SELECT booking_id,
                MAX(branch_id)                                AS branch_id,
                MAX(service_id)                               AS service_id,
                MAX(staff_id)                                 AS staff_id,
                MAX(IF(branch_id IS NOT NULL, rating, NULL))  AS branch_rating,
                MAX(IF(service_id IS NOT NULL, rating, NULL)) AS service_rating,
                MAX(IF(staff_id IS NOT NULL, rating, NULL))   AS staff_rating
         FROM booking_rating # assuming the table name
         GROUP BY booking_id
     ) AS booking_derived
         # assuming "branch", "service", "staff" as table names.
         JOIN branch ON branch.id = booking_derived.branch_id
         JOIN service ON service.id = booking_derived.service_id
         JOIN staff ON staff.id = booking_derived.staff_id;

注意：我假设了表名，因为有问题缺少此信息。您必须根据您的架构替换表名。

推荐阅读