SQL中按日期排序 [英] Sort by Date in SQL

问题描述

我有一个资源表，其中一个字段是数据类型为日期的日期字段.我想有以下输出:

I have a resources table, one of the fields is a date field with the Data Type of date. I want to have to following output:

当月记录(比如五月 - 年份并不重要)

Current month records (say May - year is not important)

然后如下(再次假设 May 是当前月份)

Then the following (again, assuming May is the current month)

• 六月记录
• 七月记录
• 八月记录
• 九月记录
• 十月记录
• 11 月记录
• 12 月记录
• 一月记录
• 二月记录
• 三月记录
• 四月记录

六月，六月是当月，然后顺序是:

Come June, June is the current month and then the order would be:

• 七月记录
• 八月记录
• ...

这是我的 SQL...我不知道如何对输出进行排序以达到所需的顺序 (5,6,7,8,9,10,11,12,1,2,3,4):

Here is my SQL...I don't know how to ORDER the output to achieve the desired order (5,6,7,8,9,10,11,12,1,2,3,4):

SELECT
  resource_id,
  resource_title,
  resource_summary,
  resource_category,
  resource_status,
  resource_date,
  DATEPART(month, resource_date) AS resource_month,
  DATEPART(day, resource_date) AS resource_day
FROM dbo.resources
WHERE (resource_category = N'Quotes')
  AND (resource_status <> N'Draft')

我为 MySQL 找到了这个可能的解决方案:

I found this possible solution for MySQL:

我需要不寻常的排序mysql结果

但我最后遗漏了一些东西.

but I'm missing something on my end.

推荐答案

ORDER BY
  (MONTH(resource_date) - MONTH(GETDATE()) + 12) % 12,
  DATEADD(year, YEAR(GETDATE()) - YEAR(resource_date), resource_date),
  YEAR(resource_date)

第一项按resource_date的月份设置主要顺序(当前月份将是第一个，上一个，最后一个).第二项对一个月内的时间戳进行排序，而不考虑日期的年份.如果您的日期不包含时间部分或者时间部分绝对不相关，您可以将其替换为 DAY(resource_date).最后，最后一项将年份考虑在内，否则相同的日期(也可以只是 resource_date).

The first term sets the primary order by the month of resource_date (the current month will be first, the previous one, last). The second term orders the timestamps within a month regardless of the year of the date. If your dates do not contain time parts or if the time parts are absolutely irrelevant, you could replace it with DAY(resource_date). Finally, the last term takes the year into account for otherwise identical dates (could also be simply resource_date).

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