如何在SQL中按年份和月份排序 [英] How to sort year and month order by in 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)

这篇关于如何在SQL中按年份和月份排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！