SQL Server : select distinct mon-yyyy format output sorty by descending order

Question

我的表中有 datetime 列，其中包含以下数据:

2011-03-23
2011-04-19
2011-04-26
2011-05-26

我想选择按报告日期降序排列的不同 mon-yyyy 格式输出。我们只需在 SQL 语句中选择一列

此 SQL 有效，但我想按 ReportDate 列排序

SELECT  distinct SUBSTRING (convert(varchar, ReportDate, 100),1,3) +'-'+
        SUBSTRING (convert(varchar, ReportDate, 100),8,4 ) 
  FROM [EnvelopsDB].[dbo].[Envelopes]

输出

Apr-2011
Mar-2011
May-2011

此 SQL 出现错误:

SELECT  distinct SUBSTRING (convert(varchar, ReportDate, 100),1,3) +'-'+
        SUBSTRING (convert(varchar, ReportDate, 100),8,4 ) 
  FROM [EnvelopsDB].[dbo].[Envelopes]
  order by ReportDate

错误:

Msg 145, Level 15, State 1, Line 2
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

获得我需要的输出的最佳 SQL 查询是什么？

Answer 1

with testdata as
(
  select cast('2011-03-23' as datetime) as d
union all
  select cast('2011-04-19' as datetime)
union all
  select cast('2011-04-26' as datetime)
union all
  select cast('2011-05-26' as datetime)
)
SELECT DATENAME(month,d)+'-'+DATENAME(year,d)
FROM testdata
GROUP BY DATEPART(year,d), DATEPART(month,d), DATENAME(month,d),DATENAME(year,d)
ORDER BY DATEPART(year,d), DATEPART(month,d)

<小时/>

SELECT DATENAME(month,ReportDate)+'-'+DATENAME(year,ReportDate)
FROM [EnvelopsDB].[dbo].[Envelopes]
GROUP BY DATEPART(year,ReportDate), DATEPART(month,ReportDate), DATENAME(month,ReportDate),DATENAME(year,ReportDate)
ORDER BY DATEPART(year,ReportDate), DATEPART(month,ReportDate)