mysql - 我如何从我的表中获取超过 2 天的记录

Question

为什么我的间隔日不能在 mysql 语句中工作。

我想要那些超过 2 天大的。这就是我尝试的方式。

SELECT t.*, ct.*,cd.*, max(t.transactiontime) as maxtime
FROM exp_channel_titles as ct
LEFT JOIN transactions as t on (ct.entry_id = t.restaurant_id)
LEFT JOIN exp_channel_data as cd on (ct.entry_id = cd.entry_id)
WHERE t.cardid > 0 
and maxtime < ( CURDATE() - INTERVAL 2 DAY )
and t.restaurant_id > 0
and ct.status= 'open'
Group by ct.entry_id
order by maxtime desc

这是我当前的输出

TransactionID          Title           TransactionTime
23132                  Locaton         2013-05-17 10:02:04
23131                  Novuel          2013-05-16 16:26:45
23128                  Mama Rosa       2013-05-15 14:42:44
23126                  Rohan           2013-05-14 12:46:56

这是我想要的输出

TransactionID          Title           Date
23128                  Mama Rosa       2013-05-15 14:42:44
23126                  Rohan           2013-05-14 12:46:56

但是我收到了这个错误

Error Number: 1054

Unknown column 'maxtime' in 'where clause'

SELECT t.*, ct.*,cd.*, max(t.transactiontime) as maxtime FROM exp_channel_titles as ct LEFT JOIN transactions as t on (ct.entry_id = t.restaurant_id) LEFT JOIN exp_channel_data as cd on (ct.entry_id = cd.entry_id) WHERE t.cardid > 0 and maxtime < ( CURDATE() - INTERVAL 2 DAY ) and t.restaurant_id > 0 and ct.status= 'open' Group by ct.entry_id order by maxtime desc

Filename: libraries/Functions.php(656) : eval()'d code

Line Number: 46

Answer 1

尝试:

maxtime < DATE_FORMAT( NOW() - INTERVAL 2 DAY ,'%Y-%m-%d 23:59:59')

CURDATE() 返回 DATE 类型，你需要 DATETIME 类型，所以你应该使用 NOW()