gpt4 book ai didi

mysql - Order By 不使用 Top MS SQL 查询

转载 作者:行者123 更新时间:2023-12-01 00:17:59 26 4
gpt4 key购买 nike

我正在创建一个查询,它应该适合我在 MsSQL 中处理的数据表。这是我第一次使用 MsSQL,所以我试图从我的 MySQL 中找出等效的 MsSQL 查询。

我做了一个自定义查询,它将处理 MySQL 中的 LIMIT 函数到 MsSQL(希望如此)。我在数据表中使用了以下查询:

    SELECT * FROM (
SELECT top 20 CAST(DATEPART(YEAR,[DeliveryDate]) as varchar) + ' Week ' + CAST(DATEPART(WEEK,[DeliveryDate]) AS varchar) AS week_name,
AVG(DATEDIFF(day, CONVERT(date, [UnloadDate]), CONVERT(date, [DeliveryDate]))) as Average_Delivery_Days
FROM [CARGODB].[dbo].[Cargo_Transactions]
WHERE [DeliveryDate] IS NOT NULL AND [DeliveryDate] != 0
AND [UnloadDate] IS NOT NULL AND [UnloadDate] != 0 AND [DeliveryDate] > [UnloadDate] AND [Deleted] = 0 and [StageID] = 'D'
AND [RouteID] IS NOT NULL AND [RouteID] != '' AND CONVERT(date, [DeliveryDate]) BETWEEN '2016-01-01' AND GETDATE()
AND CONVERT(date, [DeliveryDate]) >= DATEADD(week, -24, getdate())
GROUP BY DATEPART(YEAR,[DeliveryDate]), DATEPART(WEEK,[DeliveryDate])
ORDER BY CAST(DATEPART(YEAR,[DeliveryDate]) as varchar) + ' Week ' + CAST(DATEPART(WEEK,[DeliveryDate]) AS varchar) ASC
) as foo
except
SELECT * FROM (
SELECT top 10 CAST(DATEPART(YEAR,[DeliveryDate]) as varchar) + ' Week ' + CAST(DATEPART(WEEK,[DeliveryDate]) AS varchar) AS week_name,
AVG(DATEDIFF(day, CONVERT(date, [UnloadDate]), CONVERT(date, [DeliveryDate]))) as Average_Delivery_Days
FROM [CARGODB].[dbo].[Cargo_Transactions]
WHERE [DeliveryDate] IS NOT NULL AND [DeliveryDate] != 0
AND [UnloadDate] IS NOT NULL AND [UnloadDate] != 0 AND [DeliveryDate] > [UnloadDate] AND [Deleted] = 0 and [StageID] = 'D'
AND [RouteID] IS NOT NULL AND [RouteID] != '' AND CONVERT(date, [DeliveryDate]) BETWEEN '2016-01-01' AND GETDATE()
AND CONVERT(date, [DeliveryDate]) >= DATEADD(week, -24, getdate())
GROUP BY DATEPART(YEAR,[DeliveryDate]), DATEPART(WEEK,[DeliveryDate])
ORDER BY CAST(DATEPART(YEAR,[DeliveryDate]) as varchar) + ' Week ' + CAST(DATEPART(WEEK,[DeliveryDate]) AS varchar) ASC
) as bar

结果是:

week_name     | Average_Delivery_Days
==================================
2017 Week 36 | 15
2017 Week 39 | 11
2017 Week 41 | 13
2017 Week 42 | 10
2017 Week 38 | 12
2017 Week 40 | 12
2017 Week 35 | 14
2017 Week 43 | 6
2017 Week 37 | 12
2017 Week 44 | 9

但我希望它按周数升序排序,因此它应该显示 36、37、38、39 等等。

我的查询中可能遗漏了什么?

感谢您的帮助。谢谢!

最佳答案

您的 ORDER BY 子句只影响 TOP 选择;它不对结果集进行排序。在查询末尾添加一个 ORDER BY 应该可以解决问题。

但是,我确实看到了另一个问题,即您要对其中包含数字的 VARCHAR 列进行排序。 VARCHAR 按字母顺序排序,因此第 2 周将出现在第 19 周之后。所以我会这样做:

SELECT 
/* Explicitly select these columns */
week_name
, Average_Delivery_Days


FROM (
SELECT top 20
/* Add separate columns for numerical year and week */
iyear = YEAR(DeliveryDate)
, iweek = DATEPART(WEEK,DeliveryDate)
, CAST(DATEPART(YEAR,[DeliveryDate]) as varchar) + ' Week ' + CAST(DATEPART(WEEK,[DeliveryDate]) AS varchar) AS week_name,
AVG(DATEDIFF(day, CONVERT(date, [UnloadDate]), CONVERT(date, [DeliveryDate]))) as Average_Delivery_Days
FROM [CARGODB].[dbo].[Cargo_Transactions]
WHERE [DeliveryDate] IS NOT NULL AND [DeliveryDate] != 0
AND [UnloadDate] IS NOT NULL AND [UnloadDate] != 0 AND [DeliveryDate] > [UnloadDate] AND [Deleted] = 0 and [StageID] = 'D'
AND [RouteID] IS NOT NULL AND [RouteID] != '' AND CONVERT(date, [DeliveryDate]) BETWEEN '2016-01-01' AND GETDATE()
AND CONVERT(date, [DeliveryDate]) >= DATEADD(week, -24, getdate())
GROUP BY DATEPART(YEAR,[DeliveryDate]), DATEPART(WEEK,[DeliveryDate])
ORDER BY CAST(DATEPART(YEAR,[DeliveryDate]) as varchar) + ' Week ' + CAST(DATEPART(WEEK,[DeliveryDate]) AS varchar) ASC
) as foo
except
SELECT * FROM (
SELECT top 10 CAST(DATEPART(YEAR,[DeliveryDate]) as varchar) + ' Week ' + CAST(DATEPART(WEEK,[DeliveryDate]) AS varchar) AS week_name,
AVG(DATEDIFF(day, CONVERT(date, [UnloadDate]), CONVERT(date, [DeliveryDate]))) as Average_Delivery_Days
FROM [CARGODB].[dbo].[Cargo_Transactions]
WHERE [DeliveryDate] IS NOT NULL AND [DeliveryDate] != 0
AND [UnloadDate] IS NOT NULL AND [UnloadDate] != 0 AND [DeliveryDate] > [UnloadDate] AND [Deleted] = 0 and [StageID] = 'D'
AND [RouteID] IS NOT NULL AND [RouteID] != '' AND CONVERT(date, [DeliveryDate]) BETWEEN '2016-01-01' AND GETDATE()
AND CONVERT(date, [DeliveryDate]) >= DATEADD(week, -24, getdate())
GROUP BY DATEPART(YEAR,[DeliveryDate]), DATEPART(WEEK,[DeliveryDate])
ORDER BY CAST(DATEPART(YEAR,[DeliveryDate]) as varchar) + ' Week ' + CAST(DATEPART(WEEK,[DeliveryDate]) AS varchar) ASC
) as bar
/* sort by the numeric columns */
ORDER BY foo.iyear, foo.iweek

关于mysql - Order By 不使用 Top MS SQL 查询,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/47661018/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com