如何对含user_id/subdriver_id的Shifts表按vehicle_id分组并返回指定结果？

核心思路 #

先明确分组规则：

• 优先以user_id + vehicle_id为分组键，只要班次存在user_id，无论是否有subdriver_id，都归到该用户的车辆组
• 仅当班次无user_id时，才以subdriver_id + vehicle_id为分组键

关联表时需确保车辆、用户/副司机记录有效，再聚合生成指定格式的结果。

SQL实现（以MySQL为例） #

SELECT
    s.vehicle_id,
    MAX(s.user_id) AS user_id,
    -- 仅当组内无user_id时才返回subdriver_id，否则为NULL
    CASE WHEN MAX(s.user_id) IS NULL THEN MAX(s.subdriver_id) ELSE NULL END AS subdriver_id,
    -- 聚合生成shift_id数组，有user_id时用user_id拼接，否则用subdriver_id
    GROUP_CONCAT(
        CASE
            WHEN s.user_id IS NOT NULL THEN CONCAT(s.user_id, '-', s.vehicle_id)
            ELSE CONCAT(s.subdriver_id, '-', s.vehicle_id)
        END
    ) AS shift_id_array
FROM Shifts s
-- 关联Vehicles表，确保车辆记录合法
JOIN Vehicles v ON s.vehicle_id = v.vehicle_id
-- 关联Users表：有user_id则匹配user_id，无则匹配subdriver_id
LEFT JOIN Users u ON 
    (s.user_id IS NOT NULL AND u.user_id = s.user_id)
    OR (s.user_id IS NULL AND u.subdriver_id = s.subdriver_id)
-- 分组键：车辆ID + 优先取user_id，无则取subdriver_id
GROUP BY
    s.vehicle_id,
    CASE 
        WHEN s.user_id IS NOT NULL THEN s.user_id 
        ELSE s.subdriver_id 
    END;

PostgreSQL版本适配 #

如果使用PostgreSQL，将GROUP_CONCAT替换为数组聚合函数ARRAY_AGG即可：

SELECT
    s.vehicle_id,
    MAX(s.user_id) AS user_id,
    CASE WHEN MAX(s.user_id) IS NULL THEN MAX(s.subdriver_id) ELSE NULL END AS subdriver_id,
    ARRAY_AGG(
        CASE
            WHEN s.user_id IS NOT NULL THEN CONCAT(s.user_id, '-', s.vehicle_id)
            ELSE CONCAT(s.subdriver_id, '-', s.vehicle_id)
        END
    ) AS shift_id_array
FROM Shifts s
JOIN Vehicles v ON s.vehicle_id = v.vehicle_id
LEFT JOIN Users u ON 
    (s.user_id IS NOT NULL AND u.user_id = s.user_id)
    OR (s.user_id IS NULL AND u.subdriver_id = s.subdriver_id)
GROUP BY
    s.vehicle_id,
    CASE 
        WHEN s.user_id IS NOT NULL THEN s.user_id 
        ELSE s.subdriver_id 
    END;

结果说明 #

• 当组内班次存在user_id（无论是否有subdriver_id）：返回user_id、vehicle_id、shift_id_array，subdriver_id字段为NULL
• 当组内班次仅存在subdriver_id：返回subdriver_id、vehicle_id、shift_id_array，user_id字段为NULL

内容的提问来源于stack exchange，提问作者Lody Chi