mysql - 创建处理具有多个事件的日历的 MySQL 查询

Question

我正在尝试创建一个生成的查询:

VENUE | 2012-10-01 | 2012-10-02 | 2012-10-03
 01   |  OCCUPIED  |   EMPTY    |  OCCUPIED
 02   |   EMPTY    |   EMPTY    |  OCCUPIED
 03   |  OCCUPIED  |   EMPTY    |   EMPTY

来自这 2 个表:

field 表:

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

我做错了什么？

Answer 1

奇怪的是，您的 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 和 venue 字段，并且您的查询按 field 分组，这样它会抑制“2012-10-03”日的 OCCUPIED 结果。

event_id |    start   |    end     | venue_id
   1     | 2012-10-01 | 2012-10-02 |    1
   2     | 2012-10-03 | 2012-10-04 |    1

SQL FIDDLE:http://sqlfiddle.com/#!2/dc33f/20

您可以尝试添加像 MAX 这样的聚合函数来获取记录子集上包含的任何 OCCUPIED 事件:

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.venue

SQL FIDDLE:http://sqlfiddle.com/#!2/dc33f/22