T-SQL查询中月/年对的排序

I am writing a stored procedure for displaying month and year. It is working, but it is not returning the rows in the desired order.

我正在编写一个存储过程，用于显示月份和年份。它正在工作，但是它没有按照期望的顺序返回行。

ALTER procedure [dbo].[audioblog_getarchivedates]  
as  
begin  
select DateName(Month,a.createddate) + ' ' + DateName(Year,a.createddate) as ArchiveDate 
from audio_blog a 
group by DateName(Month,a.createddate) + ' ' + DateName(Year,a.createddate) 
order by DateName(Month,a.createddate) + ' ' + DateName(Year,a.createddate) desc
end

Results will come like this:

结果如下:

March 2010
January 2010
February 2010

2010年3月2010年1月2010年2月

But that is not in a order (desc).

但这并不是有序的(desc)。

6 个解决方案

#1

Not the best solution but should work until you figure it out.

这不是最好的解决方案，但在你弄清楚之前应该是可行的。

ALTER PROCEDURE [dbo].[Audioblog_getarchivedates] 
AS 
  BEGIN 
    SELECT DISTINCT Datename(MONTH,a.DATE) + ' ' + Datename(YEAR,a.DATE) AS archivedate,
    Cast(Datename(MONTH,a.DATE) + ' ' + Datename(YEAR,a.DATE) AS DATETIME) AS tmp
    FROM audio_blog a 
    ORDER BY tmp DESC 
  END

#2

Jeff Smith has written an excellent article on methods how to group and order by Year-Month pairs. I like especially his approach of rounding the date to the first day of the month and grouping by that value:

杰夫·史密斯写了一篇关于如何按月对进行分组和订购的方法的优秀文章。我特别喜欢他把日期四舍五入到一个月的第一天并按这个值分组的方法:

GROUP BY dateadd(month, datediff(month, 0, SomeDate),0)

It's faster because it doesn't require string concatenation.

它更快，因为它不需要字符串连接。

You can convert this value to a year-month combination later on in the query by executing YEAR and MONTH on it for a user-friendly representation.

您可以将这个值转换为一个年月的组合，然后在查询中执行一年和一个月，以获得用户友好的表示。

#3

You want to order by a datetime value, but your groups don't have that. One way is to pick one of the createddates arbitrarily (e.g. the MAX) within each group, and ordewr by those:

您希望按datetime值排序，但您的组没有这个值。一种方法是在每个组中任意选择一个已创建的日期(例如MAX)，然后根据这些日期进行ordewr:

select ArchiveDate from (
select DateName(Month,a.createddate) + ' ' + DateName(Year,a.createddate) as ArchiveDate, MAX(createddate) as createddate
from (select CONVERT(datetime,createddate) as createddate from (select '20100101' as createddate union all select '20100201' union all select '20100301') t) a 
group by DateName(Month,a.createddate) + ' ' + DateName(Year,a.createddate) ) g
order by g.createddate desc

#4

I solve this type of problem with a subquery. The inner query has both the DisplyName and SortIndex. The outer query displays the name but sorts on the index.

我用子查询来解决这类问题。内部查询同时具有DisplyName和SortIndex。外部查询显示名称，但对索引进行排序。

e.g

如

select a,b,c DisplayName   from
(select a, b,c, DisplayName, SortIndex....

) as Temp
order by Temp.SortIndex

You can use this general approach with any number of fields simply by adding a display/index pair

只需添加一个显示/索引对，就可以对任意数量的字段使用这种通用方法

#5

End it with:

结束:

order by a.createddate desc

#6

-2

order by 1 (desc)

命令1(desc)

#1