我有一个表:订单,需要提出请求并获取其他表。我的数据库表:
id close
1 2012-05-29 03:11:15
2 2012-05-30 03:11:40
3 2012-05-31 03:12:10
4 2012-05-31 03:14:13
5 2012-05-31 03:16:50
6 2012-05-31 03:40:07
7 2012-05-31 05:22:18
8 2012-05-31 05:22:22
9 2012-05-31 05:22:50
...
我需要提出请求并获取此表(按天分组(关闭)):
1 2012-05-29 03:11:15
2 2012-05-30 03:11:40
9 2012-05-31 05:22:50 /*This is a last record on this day (05-31)*/
谢谢!
如果我提出此请求:
SELECT id, close
FROM `orders`
GROUP BY DAY(close)
ORDER BY id ASC
我会得到这个表:
1 2012-05-29 03:11:15
2 2012-05-30 03:11:40
3 2012-05-31 03:12:10
尝试:
select t1.*
from orders t1
join (
select max(close) as close
from orders
group by date(close)
) t2 on t1.close = t2.close
工作示例:http://sqlfiddle.com/#!2/e799a/1
试试这个,这样就可以了。
SELECT
a.id,
a.close
FROM
(
SELECT
id,
close
FROM
`orders`
ORDER BY
close DESC
) AS a
GROUP BY
DATE(a.close)
ORDER BY
a.id
ASC;
也许这会有所帮助:
SELECT temp.`close`
FROM (
SELECT `close`
FROM `orders`
ORDER BY `close` DESC
) AS temp
ORDER BY `close` ASC