如何在 MySQL 中按月排序
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13189986/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to sort by month in MySQL
提问by shin
The order is November and October. How can I add ORDER BY to this, so that the order is by month, Jan, Feb, Mar...etc.
订单是十一月和十月。我如何将 ORDER BY 添加到这个，以便订单是按月，一月，二月，三月......等。
Thanks in advance.
提前致谢。
CREATE TABLE `hw_homework` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`studentid` int(10) NOT NULL,
`subjectid` int(10) NOT NULL,
`assignment_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`teacherid` int(10) NOT NULL,
`date` date NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=55 ;
--
-- Dumping data for table `hw_homework`
--
INSERT INTO `hw_homework` (`id`, `studentid`, `subjectid`, `assignment_name`, `teacherid`, `date`) VALUES
(52, 56, 13, '1A', 20, '2012-10-28'),
(53, 56, 6, '12', 18, '2012-10-28'),
(54, 56, 4, 'page42', 59, '2012-11-02');
SELECT studentID,
DATE_FORMAT(`date`,'%M') `month`,
COUNT(studentid) totalMissed
FROM hw_homework
WHERE studentid = 56
GROUP BY studentid, DATE_FORMAT(`date`, '%M')
回答by shin
As Chuidiang suggested I added the following to get what I wanted. Thanks everyone.
正如垂电所建议的那样，我添加了以下内容以获得我想要的。谢谢大家。
ORDER BY Month(date)
回答by Rahul Tapali
In your case it is ordering based on aplhabets. Following will give correct result.
在您的情况下，它是根据 aplhabets 订购的。以下将给出正确的结果。
SELECT studentID,
DATE_FORMAT(`date`,'%M') `month`,
COUNT(studentid) totalMissed
FROM hw_homework
WHERE studentid = 56
GROUP BY studentid, DATE_FORMAT(`date`, '%M')
ORDER BY DATE_FORMAT(`date`,'%m')
回答by Sujathan R
Use as follows in your query
在您的查询中使用如下
GROUP BY MONTH(date)
回答by TechMan
Stumbled across this post trying to find the solution to sort by Month name. Probably not very elegant solution, but you can use the FIELDstring function to get that result set.
偶然发现这篇文章试图找到按月份名称排序的解决方案。可能不是很优雅的解决方案，但您可以使用FIELD字符串函数来获取该结果集。
SELECT
studentID,
DATE_FORMAT(`date`, '%M') `month`,
COUNT(studentid) totalMissed FROM
hw_homework WHERE
studentid = 56 GROUP BY
studentid,
DATE_FORMAT(`date`, '%M') ORDER BY
FIELD(
DATE_FORMAT(`date`, '%M'),
'January',
'February',
'March',
'April',
'May',
'June',
'July',
'August',
'September',
'October',
'November',
'December'
);
Try that. Also you can perhaps replace DATE_FORMAT with the easier function MONTHNAME(date) for readability purposes.
试试那个。此外，date出于可读性目的，您也可以将 DATE_FORMAT 替换为更简单的函数 MONTHNAME( )。
回答by jacek_podwysocki
SELECT studentID,
DATE_FORMAT(`date`,'%M') `month`,
COUNT(studentid) totalMissed
FROM hw_homework
WHERE studentid = 56
GROUP BY MONTH(DATE) ASC
回答by SRIRAM
SELECT studentID,
DATE_FORMAT(`date`,'%M') as `month`,
COUNT(studentid) totalMissed
FROM hw_homework
WHERE studentid = 56
order by Month(month)
GROUP BY studentid, DATE_FORMAT(`date`, '%M')
回答by Jayanth Ramachandran
mysql> select * from hw_homework order by date asc; //increasing order
mysql> select * from hw_homework order by date asc; //递增顺序
OR
或者
mysql> select * from hw_homework order by date desc;
mysql> select * from hw_homework order by date desc;
