mysql按照月份排序,MySQL按年/月/日排序

I have a large list of dates of the format dd-mm-yyyy.

So I want to order on: year, then on month and then on day.

Date and month are in the same field and year is an other field.

I have now: ORDER BY table.year ASC, table.date ASC

The result is that the list is order on year and then days.

How to split/strip the dd-mm format and first sort on month before sorting on days?

Same record:

date | year

dd-mm | yyyy

解决方案

based on your example you can sort the record like this,

ORDER BY STR_TO_DATE(CONCAT(year, '-', date), '%Y-%d-%m') ASC