mysql - 派生表查询在mysql中不起作用

Question

以下查询导致错误。

select monstart,
       sum(datediff(least(m.monend, t.end_date) + interval 1 day,
                    greatest(m.monstart, t.start_date)
                   )
           ) as days_worked
from travel t join
     (select date('2016-01-01') as monstart, date('2016-01-31') as monend union all
      select date('2016-02-01') as monstart, date('2016-02-29') as monend union all
 select date('2016-03-01') as monstart, date('2016-03-31') as monend union all
 select date('2016-04-01') as monstart, date('2016-04-30') as monend union all
 select date('2016-05-01') as monstart, date('2016-05-31') as monend union all
 select date('2016-06-01') as monstart, date('2016-06-30') as monend union all
 select date('2016-07-01') as monstart, date('2016-07-31') as monend union all
 select date('2016-08-01') as monstart, date('2016-08-31') as monend union all
 select date('2016-09-01') as monstart, date('2016-09-30') as monend union all
 select date('2016-10-01') as monstart, date('2016-10-31') as monend union all
 select date('2016-11-01') as monstart, date('2016-11-30') as monend union all
 select date('2016-12-01') as monstart, date('2016-12-31') as monend union all
     ) m
     on t.end_date >= m.monstart and t.start_date <= m.monend
group by m.monstart;

错误如下

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') m
     on t.end_date >= m.monstart and t.start_date <= m.monend
group by m.mon' at line 19

Answer 1

最后你有一个额外的union all。只需将其删除即可。

select monstart,
       sum(datediff(least(m.monend, t.end_date) + interval 1 day,
                    greatest(m.monstart, t.start_date)
                   )
           ) as days_worked
from travel t join
     (select date('2016-01-01') as monstart, date('2016-01-31') as monend union all
      select date('2016-02-01') as monstart, date('2016-02-29') as monend union all
 select date('2016-03-01') as monstart, date('2016-03-31') as monend union all
 select date('2016-04-01') as monstart, date('2016-04-30') as monend union all
 select date('2016-05-01') as monstart, date('2016-05-31') as monend union all
 select date('2016-06-01') as monstart, date('2016-06-30') as monend union all
 select date('2016-07-01') as monstart, date('2016-07-31') as monend union all
 select date('2016-08-01') as monstart, date('2016-08-31') as monend union all
 select date('2016-09-01') as monstart, date('2016-09-30') as monend union all
 select date('2016-10-01') as monstart, date('2016-10-31') as monend union all
 select date('2016-11-01') as monstart, date('2016-11-30') as monend union all
 select date('2016-12-01') as monstart, date('2016-12-31') as monend -- removed a union all from here
     ) m
     on t.end_date >= m.monstart and t.start_date <= m.monend
group by m.monstart;