mysql - 将日期格式添加到 FROM_UNIXTIME 中断查询

Question

尝试运行此查询:

SELECT FROM_UNIXTIME(offers_consumers_history.date,
                     `'%d-%m-%Y')` AS date,
                     COUNT(*) AS COUNT
FROM (`offers_history`)
JOIN `offers_consumers_history` ON `offers_consumers_history`.`offer_history_id`=`offers_history`.`id`
WHERE `offers_history`.`merchant_id` = 1
  AND `offers_history`.`offer_type_id` = 1
GROUP BY DATE(FROM_UNIXTIME(offers_consumers_history.date))
ORDER BY `offers_consumers_history`.`date`

如果我在我的第一个 FROM_UNIXTIME(%d-%m-%Y 部分)没有日期格式的情况下运行它，一切运行正常，但我显然没有得到正确的日期格式显示，返回的错误是:

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 'FROM (`offers_history`) JOIN `offers_consumers_history` ON `offers_consumers_his' at line 2

没有日期格式，我得到的结果如下:

{"date":"2010-08-18 18:40:00","count":"2"}

我想要的是:

{"date":"18-08-2010","count":"2"}

Answer 1

count 是一个 reserved word ，你不能用它作为别名，使用不同的名字

'%d-%m-%Y') AS dt,
COUNT(*) AS cnt

或用反引号

(offers_consumers_history.date,'%d-%m-%Y') AS `date`,
COUNT(*) AS `COUNT`