|
我有两个MySQL(MyIsAm)表,分别代表出租单位和预订：
> LettingUnits(ID,名称等)
> LettingUnitBookings(ID,F_LU_ID,开始,结束)
其中F_LU_ID是该单元的外键.
搜索特定时段内可用单位的最佳方法是什么？搜索通过开始,结束和持续时间.
>开始=最早开始预定
>结束=预订的最后结束
>持续时间=预订持续时间
我想知道是否有可能在MySQL中执行此操作,但是如果不能,那么最好的方法是在PHP中执行此操作.
例
在回答下面的答案时,我觉得一个例子将有助于解释问题.
一个LettingUnit：
>(123,“ Foo Cottage”)
一些LettingUnitBookings：
>(400,123,01/01/09,05/01/09)-5天预订
>(401,10/01/09,20/01/09)-10天预订
>(402,25/01/09,30/01/09)-5天预订
如果我们搜索：
>开始= 01/01/09
>结束= 01/02/2009
>持续时间= 5(天)
然后,我们要显示该单元.因为在搜索范围内可以预订5天.
如果持续时间为10,则该单元将不会显示,因为在搜索范围内没有连续10天的未预订天数.
最佳答案
这是一个可行的解决方案：
SELECT t.*,DATEDIFF(t.LatestAvailable,t.EarliestAvailable) AS LengthAvailable
FROM
(SELECT u.*,COALESCE(b1.End,@StartOfWindow) AS EarliestAvailable,COALESCE(b2.Start,@EndOfWindow) AS LatestAvailable
FROM LettingUnits u
LEFT OUTER JOIN LettingUnitBookings b1
ON (u.ID = b1.F_LU_ID AND b1.End BETWEEN @StartOfWindow AND @EndOfWindow)
LEFT OUTER JOIN LettingUnitBookings b2
ON (u.ID = b2.F_LU_ID AND b2.Start BETWEEN @StartOfWindow AND @EndOfWindow
AND b2.Start >= b1.End) -- edit: new term
) AS t
LEFT OUTER JOIN LettingUnitBookings x
ON (t.ID = x.F_LU_ID AND x.Start < t.LatestAvailable AND x.End > t.EarliestAvailable)
WHERE x.ID IS NULL AND DATEDIFF(t.LatestAvailable,t.EarliestAvailable) >= @WindowSize;
输出为：
+-----+-------------+-------------------+-----------------+-----------------+
| ID | Name | EarliestAvailable | LatestAvailable | LengthAvailable |
+-----+-------------+-------------------+-----------------+-----------------+
| 123 | Foo Cottage | 2009-01-05 | 2009-01-10 | 5 |
| 123 | Foo Cottage | 2009-01-20 | 2009-01-25 | 5 |
| 456 | Bar Cottage | 2009-01-20 | 2009-01-31 | 11 |
+-----+-------------+-------------------+-----------------+-----------------+
用EXPLAIN对此进行分析表明,它很好地利用了索引：
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 9 | Using where |
| 1 | PRIMARY | x | ref | F_LU_ID | F_LU_ID | 8 | t.ID | 2 | Using where; Not exists |
| 2 | DERIVED | u | system | NULL | NULL | NULL | NULL | 1 | |
| 2 | DERIVED | b1 | ref | F_LU_ID | F_LU_ID | 8 | const | 0 | |
| 2 | DERIVED | b2 | ref | F_LU_ID | F_LU_ID | 8 | const | 0 | |
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------------------------+
与@martin clayton的given解决方案的EXPLAIN报告进行比较：
+----+--------------+---------------------+--------+---------------+---------+---------+------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------+---------------------+--------+---------------+---------+---------+------+------+---------------------------------+
| 1 | PRIMARY | lu | system | PRIMARY,ID | NULL | NULL | NULL | 1 | |
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 4 | Using where |
| 2 | DERIVED | <derived3> | ALL | NULL | NULL | NULL | NULL | 4 | Using temporary; Using filesort |
| 2 | DERIVED | <derived5> | ALL | NULL | NULL | NULL | NULL | 4 | Using where; Using join buffer |
| 5 | DERIVED | LettingUnitBookings | ALL | NULL | NULL | NULL | NULL | 3 | |
| 6 | UNION | LettingUnitBookings | index | NULL | F_LU_ID | 8 | NULL | 3 | Using index |
| NULL | UNION RESULT | <union5,6> | ALL | NULL | NULL | NULL | NULL | NULL | |
| 3 | DERIVED | LettingUnitBookings | ALL | NULL | NULL | NULL | NULL | 3 | |
| 4 | UNION | LettingUnitBookings | index | NULL | F_LU_ID | 8 | NULL | 3 | Using index |
| NULL | UNION RESULT | <union3,4> | ALL | NULL | NULL | NULL | NULL | NULL | |
+----+--------------+---------------------+--------+---------------+---------+---------+------+------+---------------------------------+
通常,您要避免强制使用文件排序或使用临时的优化计划,因为这些计划会降低性能.使用GROUP BY的查询几乎可以肯定会导致这种优化,至少在MySQL中如此.
（编辑：辽源站长网）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
