sql - 如何获取格式为 MM/DD/YYYY 的最新日期顺序

Question

IF OBJECT_ID('TEMPDB..#CommaDate') IS NOT NULL
    DROP TABLE #CommaDate

CREATE TABLE #CommaDate
(
    DateFormate DATE
)

INSERT INTO #CommaDate VALUES('2018-02-24'),('2018-01-10'),('2017-05-23'),('2017-04-06')

SELECT DISTINCT STUFF((SELECT DISTINCT ',' + CONVERT(VARCHAR(20),DateFormate,101) 
              FROM #CommaDate 
              ORDER BY ',' + CONVERT(VARCHAR(20),DateFormate,101) DESC 
              FOR XML PATH('')), 1,1, '')
FROM #CommaDate

我期望的是甲酸盐的最新订单日期 MM/DD/YYY

02/24/2018,01/10/2018,05/23/2017,04/06/2017

Answer 1

由于数据类型是DATE，您可以轻松地对子查询内的结果进行分组和排序。显然，您期望得到 1row x 1col 结果，因此您可以放弃外部 FROM 子句:

SELECT STUFF((SELECT ',' + CONVERT(VARCHAR(20), DateFormate, 101)
    FROM #CommaDate
    GROUP BY DateFormate
    ORDER BY DateFormate DESC
    FOR XML PATH('')
), 1, 1, '')

输出:

(No column name)
02/24/2018,01/10/2018,05/23/2017,04/06/2017