mysql-每个周期最小值的最大值

我的问题是：
假设我需要在特定的时间内找到最佳的可能性（物品可以提供的最大分配），在这个例子中是10天。用户希望休假5天。
当前查询的示例结果集显示每天的分配：

+---------+------------+-----------+
| Item Id |    Date    | Allotment |
+---------+------------+-----------+
|   10    | 20-10-2018 |    100    |
|   10    | 21-10-2018 |    80     |
|   10    | 22-10-2018 |    100    |
|   10    | 23-10-2018 |    100    |
|   10    | 24-10-2018 |    100    |
|   10    | 25-10-2018 |    100    |
|   10    | 26-10-2018 |    100    |
|   10    | 27-10-2018 |    70     |
|   10    | 28-10-2018 |    100    |
|   10    | 29-10-2018 |    100    |
+---------+------------+-----------+

以上示例的结果集通过以下查询完成：

foreach($arrivalDates as $key => $date) {
            $arrivalDate = $date['from'];
            $departureDate = $date['till'];

            $allotmentSQLArr[] = "EXISTS (SELECT 1 FROM availability ea WHERE fkItemRoom = ir.id AND ea.arrival_date BETWEEN $arrivalDate AND $departureDate AND allotment > 0 HAVING COUNT(ea.fkItemRoom) >= :duration)";
            $whereSqlArr[] = "(
                EXISTS (SELECT fkItemRoom FROM availability ea WHERE fkItemRoom = ir.id AND ea.arrival_date = $arrivalDate AND ea.arrival_possible = 1 AND ((ea.is_maximum IS NULL AND ea.minimum_stay <= :duration) OR (ea.is_maximum = 1 AND ea.minimum_stay = :duration)))
                        AND 
                EXISTS (SELECT fkItemRoom FROM availability ea WHERE fkItemRoom = ir.id AND ea.arrival_date = $departureDate AND ea.departure_possible = 1)
                )";
        }

        $query = "
            SELECT
                t.fkItemId,
                t.arrival_date,
                SUM(t.total_allotment) as total_allotment
            FROM (
                SELECT
                    ir.fkItemId,
                    ir.id,
                    ea.arrival_date,
                    (ea.allotment * r.max_occupancy) as total_allotment
                FROM 
                    item_room ir
                INNER JOIN
                    room r
                ON 
                    r.id = ir.fkRoomId
                INNER JOIN
                    availability as ea
                ON
                    ea.fkItemRoom = ir.id
                AND
                    ea.arrival_date BETWEEN :begin_date AND :end_date
                AND
                    ea.allotment > 0
                AND 
                    (".implode(' OR ', $whereSqlArr).")
                WHERE
                    (".implode(' OR ', $allotmentSQLArr).")
            ) as T
            GROUP BY
                t.fkItemId, t.arrival_date
        ";

解决方案我在寻找：现在我需要知道，如果该项目可以为用户提供5天的停留，并有至少80分配每天可用。2018年10月20日至2018年10月24日、2018年10月21日至2018年10月25日、2018年10月22日至2018年10月26日期间，最大值为80。在2018年10月23日至2018年10月27日期间，最大值为70。
在这种情况下，项目应该是可用的，因为它至少可以在一个期间内为用户提供分配。
我自己的方法：

SELECT
            a.id
        FROM
         tl_item a
         WHERE 
            EXISTS (
                SELECT
                    1
                FROM (
                    SELECT
                        t.fkItemId,
                        t.arrival_date,
                        SUM(t.total_allotment) as total_allotment
                    FROM (
                        SELECT
                            ir.fkItemId,
                            ir.id,
                            ea.arrival_date,
                            (ea.allotment * r.max_occupancy) as total_allotment
                        FROM 
                            tl_item_room ir
                        INNER JOIN
                            tl_room r
                        ON 
                            r.id = ir.fkRoomId
                        INNER JOIN
                            tl_et_availability as ea
                        ON
                            ea.fkItemRoom = ir.id
                        AND
                            ea.arrival_date BETWEEN :begin_date AND :end_date
                        AND
                            ea.allotment > 0
                        AND 
                            ((
                EXISTS (SELECT fkItemRoom FROM tl_et_availability ea WHERE fkItemRoom = ir.id AND ea.arrival_date = 1539853200 AND ea.arrival_possible = 1 AND ((ea.is_maximum IS NULL AND ea.minimum_stay <= :duration) OR (ea.is_maximum = 1 AND ea.minimum_stay = :duration)))
                        AND 
                EXISTS (SELECT fkItemRoom FROM tl_et_availability ea WHERE fkItemRoom = ir.id AND ea.arrival_date = 1540458000 AND ea.departure_possible = 1)
                ) OR (
                EXISTS (SELECT fkItemRoom FROM tl_et_availability ea WHERE fkItemRoom = ir.id AND ea.arrival_date = 1539939600 AND ea.arrival_possible = 1 AND ((ea.is_maximum IS NULL AND ea.minimum_stay <= :duration) OR (ea.is_maximum = 1 AND ea.minimum_stay = :duration)))
                        AND 
                EXISTS (SELECT fkItemRoom FROM tl_et_availability ea WHERE fkItemRoom = ir.id AND ea.arrival_date = 1540544400 AND ea.departure_possible = 1)
                ) OR (
                EXISTS (SELECT fkItemRoom FROM tl_et_availability ea WHERE fkItemRoom = ir.id AND ea.arrival_date = 1540026000 AND ea.arrival_possible = 1 AND ((ea.is_maximum IS NULL AND ea.minimum_stay <= :duration) OR (ea.is_maximum = 1 AND ea.minimum_stay = :duration)))
                        AND 
                EXISTS (SELECT fkItemRoom FROM tl_et_availability ea WHERE fkItemRoom = ir.id AND ea.arrival_date = 1540630800 AND ea.departure_possible = 1)
                ) OR (
                EXISTS (SELECT fkItemRoom FROM tl_et_availability ea WHERE fkItemRoom = ir.id AND ea.arrival_date = 1540112400 AND ea.arrival_possible = 1 AND ((ea.is_maximum IS NULL AND ea.minimum_stay <= :duration) OR (ea.is_maximum = 1 AND ea.minimum_stay = :duration)))
                        AND 
                EXISTS (SELECT fkItemRoom FROM tl_et_availability ea WHERE fkItemRoom = ir.id AND ea.arrival_date = 1540720800 AND ea.departure_possible = 1)
                ) OR (
                EXISTS (SELECT fkItemRoom FROM tl_et_availability ea WHERE fkItemRoom = ir.id AND ea.arrival_date = 1540198800 AND ea.arrival_possible = 1 AND ((ea.is_maximum IS NULL AND ea.minimum_stay <= :duration) OR (ea.is_maximum = 1 AND ea.minimum_stay = :duration)))
                        AND 
                EXISTS (SELECT fkItemRoom FROM tl_et_availability ea WHERE fkItemRoom = ir.id AND ea.arrival_date = 1540807200 AND ea.departure_possible = 1)
                ))
                        AND
                            (EXISTS (SELECT 1 FROM tl_et_availability ea WHERE fkItemRoom = ir.id AND ea.arrival_date BETWEEN 1539853200 AND 1540458000 AND allotment > 0 HAVING COUNT(ea.fkItemRoom) >= :duration) OR EXISTS (SELECT 1 FROM tl_et_availability ea WHERE fkItemRoom = ir.id AND ea.arrival_date BETWEEN 1539939600 AND 1540544400 AND allotment > 0 HAVING COUNT(ea.fkItemRoom) >= :duration) OR EXISTS (SELECT 1 FROM tl_et_availability ea WHERE fkItemRoom = ir.id AND ea.arrival_date BETWEEN 1540026000 AND 1540630800 AND allotment > 0 HAVING COUNT(ea.fkItemRoom) >= :duration) OR EXISTS (SELECT 1 FROM tl_et_availability ea WHERE fkItemRoom = ir.id AND ea.arrival_date BETWEEN 1540112400 AND 1540720800 AND allotment > 0 HAVING COUNT(ea.fkItemRoom) >= :duration) OR EXISTS (SELECT 1 FROM tl_et_availability ea WHERE fkItemRoom = ir.id AND ea.arrival_date BETWEEN 1540198800 AND 1540807200 AND allotment > 0 HAVING COUNT(ea.fkItemRoom) >= :duration))
                    ) as T
                    GROUP BY
                        t.fkItemId,t.arrival_date
                    )  as B
                WHERE
                    ((arrival_date BETWEEN 1539853200 AND 1540458000 and total_allotment > :allotment) OR (arrival_date BETWEEN 1539939600 AND 1540544400 and total_allotment > :allotment) OR (arrival_date BETWEEN 1540026000 AND 1540630800 and total_allotment > :allotment) OR (arrival_date BETWEEN 1540112400 AND 1540720800 and total_allotment > :allotment) OR (arrival_date BETWEEN 1540198800 AND 1540807200 and total_allotment > :allotment))
                AND
                b.fkItemId = a.id

            )

我知道，如果只有一个记录符合条件，上述结果将返回true。我需要实现的是，以下行检查这些日期之间的所有记录是否分配了更大的分配：

WHERE ((arrival_date BETWEEN 1539853200 AND 1540458000 and total_allotment >= :allotment) OR (arrival_date BETWEEN 1539939600 AND 1540544400 and total_allotment >= :allotment) OR (arrival_date BETWEEN 1540026000 AND 1540630800 and total_allotment >= :allotment) OR (arrival_date BETWEEN 1540112400 AND 1540720800 and total_allotment >= :allotment) OR (arrival_date BETWEEN 1540198800 AND 1540807200 and total_allotment >= :allotment))

mysql-每个周期最小值的最大值

暂无答案！

相关问题

热门标签

最新问答