mysql怎么以天分组_MYSQL要求|按天分组

I have a table: orders, and need to make a request and get other table.

My DB table:

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

...

I need to make a request and get this table (GROUP BY DAY(close)):

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)*/

Thanks!

If I make this request:

SELECT id, close

FROM `orders`

GROUP BY DAY(close)

ORDER BY id ASC

I will get this table:

1 2012-05-29 03:11:15

2 2012-05-30 03:11:40

3 2012-05-31 03:12:10

解决方案

Try this, this will do.

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;