你好,我有一张桌子,上面有:
id_order | id_order_history | date_add
通过这个查询,我可以获得所有具有id_order_history = 3
(准备中)的订单
SELECT
`oh`.`id_order`
FROM (
SELECT MAX( `date_add` ) AS date_add
FROM `'._DB_PREFIX_.'order_history`
GROUP BY `id_order`
) AS h
INNER JOIN
'._DB_PREFIX_.'order_history AS oh
ON
`h`.`date_add` = `oh`.`date_add`
INNER JOIN
'._DB_PREFIX_.'orders AS o
ON
`oh`.`id_order` = `o`.`id_order`';
$sql .= 'WHERE
oh.`id_order_state` = '.$status.'';
$sql .= ' AND
oh.date_add >= "'.$start.'"';
$sql .= ' AND
oh.date_add <= "'.$end.'"
ORDER BY
`oh`.`id_order` ASC
问题是对于某些订单,SELECT MAX
没有得到date_add
的正确值
id_order | id_order_history | date_add
1 | 5 | 25-09-2014 18:07:59 ---> this is the correct value
1 | 4 | 25-09-2014 18:07:51
1 | 3 | 25-09-2014 18:06:42 ---> but query return this
如果我在日期1秒(18:06:43)添加查询,则返回正确的值25-09-2014 18:07:59
我就是不知道怎么解决这个问题。
我读到过GROUP BY
有时会把结果搞得乱七八糟。。。
我该怎么修?
将列date_add
转换为ISO日期时间字符串和/或使用to_SECONDS(请参阅此处的MySQL文档)
...( SELECT FROM_UNIXTIME(MAX(TO_SECONDS(`date_add`)) ) AS date_add
FROM `'._DB_PREFIX_.'order_history`
GROUP BY `id_order` ) AS h