创建一个处理包含多个事件的日历的MySQL查询
创建一个处理包含多个事件的日历的MySQL查询
提问于 2012-10-07 20:23:07
我正在尝试创建一个查询，它产生以下结果：
VENUE | 2012-10-01 | 2012-10-02 | 2012-10-03
01 | OCCUPIED | EMPTY | OCCUPIED
02 | EMPTY | EMPTY | OCCUPIED
03 | OCCUPIED | EMPTY | EMPTY从这两个表中：
会场表：
venue_id | venue
1 | 01
2 | 02
3 | 03事件表：
event_id | start | end | venue_id
1 | 2012-10-01 | 2012-10-02 | 1
2 | 2012-10-03 | 2012-10-04 | 1
3 | 2012-10-03 | 2012-10-04 | 2
4 | 2012-10-01 | 2012-10-02 | 3有没有人能提出解决这个问题的最佳方法？
我目前的方法是：
SELECT
venue,
IF(start <= '2012-10-01' AND '2012-10-01' < end, 'OCCUPIED','EMPTY') AS '2012-10-01',
IF(start <= '2012-10-02' AND '2012-10-02' < end, 'OCCUPIED','EMPTY') AS '2012-10-02',
IF(start <= '2012-10-03' AND '2012-10-03' < end, 'OCCUPIED','EMPTY') AS '2012-10-03'
FROM Venue as v, Event as e
WHERE e.venue_id = v.venue_id
GROUP BY v.venue然而，我没有得到想要的结果……相反，我得到了以下内容：
VENUE | 2012-10-01 | 2012-10-02 | 2012-10-03
01 | OCCUPIED | EMPTY | EMPTY
02 | EMPTY | EMPTY | OCCUPIED
03 | OCCUPIED | EMPTY | EMPTY我做错了什么？
回答 1
Stack Overflow用户
回答已采纳
发布于 2012-10-07 23:30:35
奇怪的是，您的select语句甚至没有返回在您发布的结果集中命名为的列。此外，我注意到您在查询中重复了“2012-10-02”这一天。
SELECT
IF(start <= '2012-10-01' AND '2012-10-01' < end, 'OCCUPIED','EMPTY') AS '2012-10-01',
IF(start <= '2012-10-02' AND '2012-10-02' < end, 'OCCUPIED','EMPTY') AS '2012-10-02',
IF(start <= '2012-10-03' AND '2012-10-03' < end, 'OCCUPIED','EMPTY') AS '2012-10-03'
FROM Venue as v, Event as e
WHERE e.venue_id = v.venue_id
GROUP BY v.venue编辑：
下面的两个事件共享相同的venue_id和地点字段，并且您的查询是按地点分组的，这样就会抑制'2012-10-03‘日的占用结果。
event_id | start | end | venue_id
1 | 2012-10-01 | 2012-10-02 | 1
2 | 2012-10-03 | 2012-10-04 | 1SQL FIDDLE：http://sqlfiddle.com/#!2/dc33f/20
您可以尝试添加一个聚合函数，如MAX，以获取记录子集上包含的任何占用的事件：
SELECT e.*, v.venue,
max(IF(start <= '2012-10-01' AND '2012-10-01' < end, 'OCCUPIED','EMPTY')) AS '2012-10-01',
max(IF(start <= '2012-10-02' AND '2012-10-02' < end, 'OCCUPIED','EMPTY')) AS '2012-10-02',
max(IF(start <= '2012-10-03' AND '2012-10-03' < end, 'OCCUPIED','EMPTY')) AS '2012-10-03'
from event e,
venue v
where e.venue_id = v.venue_id
GROUP BY v.venueSQL FIDDLE：http://sqlfiddle.com/#!2/dc33f/22
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/12768781
复制相关文章
相似问题
腾讯云开发者
