基于行级动态自定义变量的子查询实现班次可用救护车统计

问题说明 #

需基于ambulances_shift_available视图，在ambulances_available count视图中实现按班次统计可用救护车数量。原方案尝试使用自定义变量，日期变量可正常工作，但@shift_id无法按行动态赋值完成分班次统计。

注：ambulances.id IN (...) IS FALSE是MySQL Workbench自动转换NOT IN的写法；CAST(...) AS DATETIME(6)是自动转换TIMESTAMP(...)的写法。

修改方案 #

1. 重构ambulances_shift_available视图 #

移除对SHIFT_ID()函数的依赖，让视图返回所有班次的可用救护车数据，便于后续关联统计：

CREATE 
    ALGORITHM = UNDEFINED 
    DEFINER = `root`@`localhost` 
    SQL SECURITY DEFINER
VIEW `ambulances_shift_available` AS
    SELECT 
        `shifts_options`.`id` AS `shift_id`,
        `shifts_options`.`timeStart` AS `timeStart`,
        `shifts_options`.`timeEnd` AS `timeEnd`,
        `shifts_options`.`locations_id` AS `locations_id`,
        `shifts_options`.`types_id` AS `types_id`,
        `ambulances`.`id` AS `ambulances_id`
    FROM
        `shifts_options`
        JOIN `ambulances` ON `ambulances`.`types_id` = `shifts_options`.`types_id` 
            AND `ambulances`.`locations_id` = `shifts_options`.`locations_id`
    WHERE
        `ambulances`.`id` NOT IN (SELECT 
                `ambulances_unavailable`.`ambulances_id`
            FROM
                `ambulances_unavailable`
            WHERE
                (TIMESTAMP(SPECIFICDATE(), `shifts_options`.`timeStart`) BETWEEN CAST(`ambulances_unavailable`.`dateStart` AS DATETIME(6)) AND CAST(`ambulances_unavailable`.`dateEnd` AS DATETIME(6)))
                OR (TIMESTAMP(SPECIFICDATE(), `shifts_options`.`timeEnd`) BETWEEN CAST(`ambulances_unavailable`.`dateStart` AS DATETIME(6)) AND CAST(`ambulances_unavailable`.`dateEnd` AS DATETIME(6)))
                OR (CAST(`ambulances_unavailable`.`dateStart` AS DATETIME(6)) BETWEEN TIMESTAMP(SPECIFICDATE(), `shifts_options`.`timeStart`) AND TIMESTAMP(SPECIFICDATE(), `shifts_options`.`timeEnd`))
                OR (CAST(`ambulances_unavailable`.`dateEnd` AS DATETIME(6)) BETWEEN TIMESTAMP(SPECIFICDATE(), `shifts_options`.`timeStart`) AND TIMESTAMP(SPECIFICDATE(), `shifts_options`.`timeEnd`)));

2. 重新定义ambulances_available count视图 #

通过关联shifts_uncoupled和重构后的ambulances_shift_available，按班次分组统计可用救护车数量：

CREATE 
    ALGORITHM = UNDEFINED 
    DEFINER = `root`@`localhost` 
    SQL SECURITY DEFINER
VIEW `ambulances_available count` AS
    SELECT 
        su.id,
        su.date,
        su.shifts_options_id,
        COUNT(asa.ambulances_id) AS available_ambulances_count
    FROM
        `shifts_uncoupled` su
        LEFT JOIN `ambulances_shift_available` asa ON su.shifts_options_id = asa.shift_id
    GROUP BY su.id, su.date, su.shifts_options_id;

方案说明 #

原方案依赖单班次函数限制了视图的复用性，且变量动态赋值在MySQL视图中容易出现行级执行顺序问题。重构后的方案通过关联查询和分组统计，直接实现按班次统计需求，逻辑更清晰，执行效率更稳定。

期望输出 #

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