SQL order by concatenated Month and Year字符串
我有一个有趣的查询,其中包含错误的聚合命令,因为它以字母顺序排列日期,因为它们是字符串(VARCHARS)。它如下。SQL order by concatenated Month and Year字符串
DROP PROCEDURE dbo.DeploymentReport
GO
CREATE PROCEDURE dbo.DeploymentReport
@Date DATE
AS
BEGIN
SELECT TOP 6 DATENAME(MONTH,DeployDate) + ' - ' + DATENAME(YEAR,DeployDate) AS 'Month',
ISNULL(SUM(CASE WHEN OverallRiskRating = 3 THEN 1 END),0) AS 'High',
ISNULL(SUM(CASE WHEN OverallRiskRating = 2 THEN 1 END),0) AS 'Med',
ISNULL(SUM(CASE WHEN OverallRiskRating = 1 THEN 1 END),0) AS 'Low'
FROM dbo.ChangeEvaluationForm cef
INNER JOIN dbo.Package pac
ON cef.ChangeId = pac.ChangeId
INNER JOIN dbo.SDMTicket sdm
ON pac.PackageId = sdm.PackageId
WHERE DeployDate < @Date
GROUP BY DATENAME(MONTH, DeployDate) + ' - ' + DATENAME(YEAR,DeployDate)
ORDER BY DATENAME(MONTH, DeployDate) + ' - ' + DATENAME(YEAR,DeployDate) DESC
END
GO
DECLARE @DateSelected DATETIME
SET @DateSelected = CONVERT(DATE,'2017-12-30')
EXECUTE dbo.DeploymentReport @Date = @DateSelected;
结果如下所示
正如你所看到的月份按日期排序的字母数字代替。
现在我对汇总查询是绝对残酷的,所以我的斗争是改变ORDER BY行,以便以某种方式解析“月”列中的年份和月份,以及年份和月份中的订单,而不更改数据返回的结构。
这是不是可能,或者是我认为不可能的方法或者错误的思维方式?
您可以通过条款,添加year(DeployDate)
和month(DepolyDate)
到组,因为你已经在使用组datename(year, DeployDate)
和datename(month, DeployDate)
by子句应该没有效果都没有,其他的则是,它可以让你使用他们的按条款排序:
CREATE PROCEDURE dbo.DeploymentReport
@Date DATE
AS
BEGIN
SELECT TOP 6 DATENAME(MONTH,DeployDate) + ' - ' + DATENAME(YEAR,DeployDate) AS 'Month',
ISNULL(SUM(CASE WHEN OverallRiskRating = 3 THEN 1 END),0) AS 'High',
ISNULL(SUM(CASE WHEN OverallRiskRating = 2 THEN 1 END),0) AS 'Med',
ISNULL(SUM(CASE WHEN OverallRiskRating = 1 THEN 1 END),0) AS 'Low'
FROM dbo.ChangeEvaluationForm cef
INNER JOIN dbo.Package pac
ON cef.ChangeId = pac.ChangeId
INNER JOIN dbo.SDMTicket sdm
ON pac.PackageId = sdm.PackageId
WHERE DeployDate < @Date
GROUP BY DATENAME(MONTH, DeployDate) + ' - ' + DATENAME(YEAR,DeployDate), Year(DeployDate), Month(DeployDate)
ORDER BY Year(DeployDate) DESC, Month(DeployDate) DESC
END
GO
这个工程也很棒,我实际上选择使用这个解决方案来代替可读性 – David
很高兴为您提供帮助: - ) –
移GROUP BY
逻辑到使用更“日期样”分组列的子查询,然后应用TOP
和ORDER BY
在外部查询:
SELECT TOP 6 DATENAME(MONTH,DeployDate) + ' - ' + DATENAME(YEAR,DeployDate) AS 'Month',
High,Med,Low
FROM (
SELECT DATEADD(month,DATEDIFF(month,0,DeployDate),0) as DeployDate,
ISNULL(SUM(CASE WHEN OverallRiskRating = 3 THEN 1 END),0) AS 'High',
ISNULL(SUM(CASE WHEN OverallRiskRating = 2 THEN 1 END),0) AS 'Med',
ISNULL(SUM(CASE WHEN OverallRiskRating = 1 THEN 1 END),0) AS 'Low'
FROM dbo.ChangeEvaluationForm cef
INNER JOIN dbo.Package pac
ON cef.ChangeId = pac.ChangeId
INNER JOIN dbo.SDMTicket sdm
ON pac.PackageId = sdm.PackageId
WHERE DeployDate < @Date
GROUP BY DATEADD(month,DATEDIFF(month,0,DeployDate),0)
) t
ORDER BY DeployDate DESC
这里,DATEADD(month,DATEDIFF(month,0,DeployDate),0)
轮DeployDate
柱向下在相关月份的第一天开始的午夜 - 因此同一月份内的所有DeployDate
值都会生成相同的值。然后,在此列上分组就相当于对您的字符串进行分组,但它仍然是datetime
,因此可以在外部ORDER BY
中正确使用。 (而且仍然可以使用外SELECT
提取年份和月份组成部分)
这就像一个魅力。非常感谢你! – David
你的时间跨度是有序的,所以我只选择在每一个任意值,并做到:
ORDER BY MIN(DeployDate) DESC
我应该注意到我更喜欢YYYY-MM格式;一个重要的原因是它可以正确地命令。
只是不按DATENAME命令。只需使用“ORDER BY DATEPART(mm,DeployDate)”等等 –