T-SQL:按年、月对结果进行排序 [英] T-SQL: sorting results by year, month

问题描述

我有以下声明:

select
    (datename(MM, SomeDate) + ' ' + datename(day, SomeDate)) as D,
    count(SomeID) as IDs
from dbo.SomeTable
where
    datename(MM, SomeDate) = @Month
    and datename(YYYY, SomeDate) = @Year
group by
    datename(YYYY, SomeDate),
    datename(MM, SomeDate),
    datename(DD, SomeDate);

它可以工作，但它会像这样对结果进行排序:

It works but it sorts the result like this:

July 1, 2011 - 2
July 10, 2011 - 4
July 2, 2011 - 10

如何使它在 7 月 2 日之后显示为 7 月 10 日?我知道这很简单，但我找不到问题所在.感谢您的帮助！

How do I make it to display July 10 after July 2? I know it's something simple but I can't find the problem. Thanks for any help!

伙计们，我知道必须有一个order by"，我认为这很明显.但是正常"排序会产生相同的结果(见我的评论).我认为这与整理有关，但我不是这方面的专家.

Guys, I know there must be an "order by", I thought it's quite obvious. But the "normal" ordering produces the same result (see my comments). I think it's related to collation but I'm not an expert there.

推荐答案

您正在丢失 somedate 的信息并且它无论如何都无法用于 ORDER BY

You are losing information from somedate and it isn't available for ORDER BY anyway

按 DATENAME 排序不是您想要的，因为 7 月是在 8 月之后

Sorting by DATENAME isn't what you want because July is after August

因此，将更多派生列添加到 GROUP BY 以便稍后使用提供数字的 DATEPART 进行排序

So, add more derived columns to the GROUP BY to sort on later that use DATEPART which gives numbers

select
    (datename(MM, SomeDate) + ' ' + datename(day, SomeDate)) as D,
    count(SomeID) as IDs
from
    dbo.SomeTable
where
    datename(MM, SomeDate) = @Month
    and datename(YYYY, SomeDate) = @Year
group by
    datename(YYYY, SomeDate),
    datename(MM, SomeDate),
    datename(DD, SomeDate),
    datepart(YYYY, SomeDate),
    datepart(MM, SomeDate),
    datepart(DD, SomeDate)
order by
    datepart(YYYY, SomeDate),
    datepart(MM, SomeDate),
    datepart(DD, SomeDate)

这篇关于T-SQL:按年、月对结果进行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！