时间戳

首页 > 解决方案 > 计算具有相同 MySQL 的 3 列值的行的出现次数

mysql - 计算具有相同 MySQL 的 3 列值的行的出现次数

问题描述

MySQLBEFORE INSERT触发器

我有一张bookings桌子。我有 3 列称为

facility_id, booking_date, time_slot

我还有另一个facility名为 . 列的表facility_id。它们通过以下方式链接WHERE bookings.faciliy_id = facility.facility_id

我正在尝试计算预订表中的 3 列重复时的发生情况，即：

1, 2017-02-02,08:00
1, 2017-02-02,08:00

我希望这是2。

然后我试图检查这个结果，看看它是否 >= 存储在设施表中的设施容量。如果为真，则抛出错误消息。

DELIMITER
//
 CREATE TRIGGER facility_full BEFORE INSERT ON bookings
    FOR EACH ROW
        BEGIN 
        SELECT DISTINCT facility_id, booking_date, timeslot, COUNT(*) FROM bookings 
        SET COUNT = Answer
            IF (Answer) >=  facility_capacity
            THEN  SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Facility Full';
            WHERE bookings.facility_id = facility.facility_id;
            END IF;
    END 
 //
 DELIMITER ;

标签： mysqltriggerscountwheresql-insert

解决方案

正如您所说，计算预订表中的 3 列重复时的发生情况。这是解决您的问题的一种方法。

DELIMITER
//
 CREATE TRIGGER facility_full BEFORE INSERT ON bookings
    FOR EACH ROW
        BEGIN 
        DECLARE v_count int;
        Declare msg TEXT;
        DECLARE v_facility_capacity  int;


        select count(*) into v_count from bookings where facility_id = New.facility_id and booking_date  = new.booking_date  and time_slot = new.time_slot;

select facility_capacity  into v_facility_capacity from facility 
where facility.facility_id = New.facility_id;

            IF (v_count) >=  v_facility_capacity  THEN
            set msg = concat('MyTriggerError: Facility Full: ', cast(new.facility_id as char));
            signal sqlstate '45000' set message_text = msg;
            END IF;
    END 
 //
 DELIMITER ;

推荐阅读