如何在SQL中按年份和月份排序

本文介绍了如何在SQL中按年份和月份排序的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

大家好， 按顺序在sql中遇到一个问题。 实际上我想显示年份和月份的订单 我的预期输出是： 2015_Jan 2015_Feb 2015_Mar 2015_Apr 2015_May 2015_Jun 2015_Jul 2015_Aug 2015_Sep 2015_Oct 2015_Nov 2015_Dec 2016_Jan - - - 等等 但根据我的代码我没有这样， i得到了字母顺序，这意味着， 盯着A到Z 以下是我的代码，

Hi All, am facing one problem in order by in sql. Actually i want to display order by for year and month My expected output is: 2015_Jan 2015_Feb 2015_Mar 2015_Apr 2015_May 2015_Jun 2015_Jul 2015_Aug 2015_Sep 2015_Oct 2015_Nov 2015_Dec 2016_Jan -- -- etc but as per my code am not getting like that, i got the alphabetical order, which means, staring from A to Z below is my code,

select billdate from test ORDER BY YEAR(billdate),Month(billdate)

请放我一个解决方案。 问候， stellus 我尝试过： 如何按sql

please drop me a solution. with regards, stellus What I have tried: how to sort year and month order by in sql

推荐答案

对年份和月份顺序进行排序基于您的示例查询 Based on your sample query select billdate from test ORDER BY YEAR(billdate),Month(billdate)

您必须将列转换为日期才能获得正确的排序。例如。

You are going to have to convert the column to a date to get the correct ordering. E.g.

select billdate from test ORDER BY DATEPART(MM,SUBSTRING(billdate,1,4) + SUBSTRING(billdate, 6,3) + '01')

这是一个非常糟糕的方式 - billdate应该是日期或日期时间列，在这种情况下你可以做类似的事情。

This is a truly awful way to go about things - billdate should be a date or datetime column in which case you could do something like

select cast(DATEPART(YY,billdate) as varchar) + '_' + convert(char(3), datename(month, billdate)) from test ORDER BY billdate

这也很糟糕。 你应该做的是

Which is also pretty awful. What you should be doing is

select billdate from test order by billdate

并在演示文稿（UI）层中对结果进行格式化

and doing the formatting of the result in the presentation (UI) layer

那是因为你是严重存储信息：您将其存储为字符串，这意味着比较将始终基于字符串：第一个不同的字符确定整个比较的结果。 如果存在，则可以执行此操作您将年份部分提取为字符串，然后将月份部分作为单独的字符串提取。然后，您可以使用JOIN到单独的表将短月份名称转换为数字，然后使用yera和minth值进行ORDER BY。 但是......它很笨拙，而且很差理念。每次你想要访问或使用该字段时，它也会变慢。 相反，将列更改为DATE或DATETIME然后它是微不足道的： That's because you are storing information badly: you are storing it as a string, which means that the comparison will always be string based: the first different character determines the result of the whole comparison. It is possible to do it if you extract the year portion as a string, and then month part as a separate string. You can then use a JOIN to a separate table to convert the short month name to a number, and then use the yera and minth values to ORDER BY. But...it's clumsy, and a poor idea. It's also goign to be slow every time you want to access or use the field. Instead, change the column to a DATE or DATETIME and then it's trivial: SELECT BillDate FROM test ORDER BY DATEPART(yy, BillDate), DATEPART(mm, BillDate)