如何对SQL Server 2008中存储为Nvarchar的会计年度进行排序

I have stored Fiscal month as a Nvarchar column in a table and want to sort that table based on fiscal month.

我已将Fiscal month存储为表中的Nvarchar列,并希望根据会计月份对该表进行排序。

Create Table sample( id Ineger ,FiscalMonth Nvarchar(MAX) );

创建表格样本(id Ineger,FiscalMonth Nvarchar(MAX));

Ex: Table contain this data

例如:表包含此数据

Id   FiscalMonth
-----------------
1     FY15-Oct
2     FY15-Sep
3     FY15-Jul
4     FY15-Aug

Now if i sort this table on order by FiscalMonth Column Result would be:
SELECT * FROM sample ORDER BY FiscalMonth;

现在,如果按照FiscalMonth列的顺序排序此表,则结果将是:SELECT * FROM sample ORDER BY FiscalMonth;

Result:

Id   FiscalMonth
-----------------
4     FY15-Aug
3     FY15-Jul
1     FY15-Oct
2     FY15-Sep

But i want result as

但我想要结果

Expected Output:

Id   FiscalMonth
-----------------
3     FY15-Jul
4     FY15-Aug
2     FY15-Sep
1     FY15-Oct

Can somebody help me how to approach on this problem without changing the schema of table?

有人可以帮助我如何在不改变表格架构的情况下解决这个问题吗?

4 个解决方案

#1

to sort for different years and then by month the order by in MS SQL should work as below:

要按不同年份排序,然后按月在MS SQL中按顺序排序:

SELECT [Id],[FiscalMonth]
From Sample
order by convert(date,Substring([FiscalMonth],6,3)+ ' 01 ' + Substring([FiscalMonth],3,2),103)

This assumes that your FiscalMonth Column is always the same format.

这假设您的FiscalMonth列始终采用相同的格式。

#2

You should give a number for each month, distinguish them you can for the last three letter.

你应该每个月给一个数字,你可以在最后三个字母中区分它们。

SUBSTRING (FiscalMonth , 5 , 3 )

And after that sorted by those numbers.

之后按那些数字排序。

#3

Just two steps:

只需两步:

• Create a database fuction that returns the date of the first day of the fiscal month. fnGetFiscalFirstDay('FY15-Jul') -> 01 Jul 2015

创建一个数据库功能,返回会计月份第一天的日期。 fnGetFiscalFirstDay('FY15-Jul') - > 2015年7月1日

• Sort by the function:

  按功能排序:

  SELECT * FROM sample s ORDER BY fnGetFiscalFirstDay(s.FiscalMonth)

  SELECT * FROM sample s ORDER BY fnGetFiscalFirstDay(s.FiscalMonth)

#4

You can try like this in MYSQL

您可以在MYSQL中尝试这样的方法

SELECT   myDate
FROM     myTest
ORDER BY
  LEFT(myDate,2),
  CAST(MID(myDate,2) AS UNSIGNED),
  MONTH(STR_TO_DATE(RIGHT(myDate,3),'%b'))

SQL FIDDLE DEMO

SQL FIDDLE DEMO

or simply

SELECT   myDate
FROM     myTest
ORDER BY
  MONTH(STR_TO_DATE(RIGHT(myDate,3),'%b'))

SQL FIDDLE DEMO

SQL FIDDLE DEMO

You need to convert the three letter into the number i.e, Jan-1, Feb-2, Mar-3 and so on and then you can use the order by to it.

您需要将三个字母转换为数字,即1月1日,2月2日,3月3日等等,然后您可以按顺序使用它。

(Same logic can be applied in SQL Server as well i.e, take the last three charaters ie, month name and convert it into number and then use order by)

(同样的逻辑也可以在SQL Server中应用,即采用最后三个字符,即月份名称并将其转换为数字,然后使用order by)

On a side note:

旁注:

It would be a good idea to create the column as date time and then you can store the value and use the datetime function to get this done easily.

最好将列创建为日期时间,然后您可以存储该值并使用datetime函数轻松完成此操作。