mysql - 如何修复查询间隔中的语法错误？

Question

select *, 
   (select max(h.date) 
    from hour_cards as h  
    where h.subordinate_id = hour_cards.subordinate_id
      and h.date > hour_cards.date and 
   h.date - hour_cards.date <= INTERVAL 1 minute) as wrong_entry
   from hour_cards

我正在尝试进行一个查询，如果每个 worker 在进入或退出工作时读卡两次或更多次，该查询将提供每个 worker 的所有条目，但上述查询始终给我错误。

#1064 - 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 ') as wrong_entryfrom hour_cards

Answer 1

只能在 date calculation function 中使用间隔.

您的查询必须类似于:

select *, 
   (select max(h.date) 
    from hour_cards as h  
    where h.subordinate_id = hour_cards.subordinate_id
      and h.date > hour_cards.date and 
   DATE_ADD(h.date, INTERVAL 1 minute) <= hour_cards.date  as wrong_entry
   from hour_cards