我想對從 1 月到 12 月的月份進行排序，但這是我的情況：
我的查詢結果
這是我對上圖的查詢
SELECT SUM(totalAmount) AS Sales, 'January' AS NameMonth
FROM sales_tbl
WHERE MONTH(transactionDate) = 1
UNION
SELECT SUM(totalAmount) AS Sales, 'February' AS NameMonth
FROM sales_tbl
WHERE MONTH(transactionDate) = 2
UNION
SELECT SUM(totalAmount) AS Sales, 'March' AS NameMonth
FROM sales_tbl
WHERE MONTH(transactionDate) = 3
UNION
SELECT SUM(totalAmount) AS Sales, 'April' AS NameMonth
FROM sales_tbl
WHERE MONTH(transactionDate) = 4
UNION
SELECT SUM(totalAmount) AS Sales, 'May' AS NameMonth
FROM sales_tbl
WHERE MONTH(transactionDate) = 5
UNION
SELECT SUM(totalAmount) AS Sales, 'June' AS NameMonth
FROM sales_tbl
WHERE MONTH(transactionDate) = 6
UNION
SELECT SUM(totalAmount) AS Sales, 'July' AS NameMonth
FROM sales_tbl
WHERE MONTH(transactionDate) = 7
UNION
SELECT SUM(totalAmount) AS Sales, 'August' AS NameMonth
FROM sales_tbl
WHERE MONTH(transactionDate) = 8
UNION
SELECT SUM(totalAmount) AS Sales, 'September' AS NameMonth
FROM sales_tbl
WHERE MONTH(transactionDate) = 9
UNION
SELECT SUM(totalAmount) AS Sales, 'October' AS NameMonth
FROM sales_tbl
WHERE MONTH(transactionDate) = 10
UNION
SELECT SUM(totalAmount) AS Sales, 'November' AS NameMonth
FROM sales_tbl
WHERE MONTH(transactionDate) = 11
UNION
SELECT SUM(totalAmount) AS Sales, 'December' AS NameMonth
FROM sales_tbl
WHERE MONTH(transactionDate) = 12
我試圖將其包含在查詢中以解決我的問題：
ORDER BY MONTH(NameMonth)
但是，我收到一個錯誤錯誤訊息
最初，NameMonth不是表中的列。
非常感謝每個人的回答。
uj5u.com熱心網友回復：
你可以這樣做。
select DateName( month , DateAdd( month , datepart(Month, transactionDate) , -1 ) ) AS Sales, sum(totalAmount) As NameMonth from Mas_Transaction
group by datepart(Month, transactionDate)
order by datepart(Month, transactionDate)
uj5u.com熱心網友回復：
首先，使用以下方法以一行顯示每月的銷售額。之后，您可以使用 Case When 命令獲取月份名稱并根據月份 ID（MonthID 或 MONTH(transactionDate)）進行排序。嘗試遵循：
select * from(
SELECT SUM(totalAmount) AS Sales , MONTH(transactionDate) AS MonthID
FROM sales_tbl
GROUP BY MONTH(transactionDate)
) as tbl order by tbl.MonthID
uj5u.com熱心網友回復：
以下查詢使用 CTE 而不是使用子查詢/聯合，并按月和年排序以獲得更高的準確性。
說明：QUERY 是使用 CTE 對資料進行分組并使用 DATENAME 行內函式獲取 MonthName 然后執行正常的選擇查詢并對資料進行排序
WITH QUERY AS(
SELECT SUM(totalAmount) Sales,
MONTH(transactionDate) SalesMonth,
YEAR(transactionDate) SalesYear,
DATENAME(MONTH,transactionDate) NameMonth
FROM sales_tbl
GROUP BY MONTH(transactionDate),YEAR(transactionDate),DATENAME(MONTH,transactionDate)
)
SELECT Sales,NameMonth,SalesYear
FROM QUERY
ORDER BY SalesYear,SalesMonth
參考：
日期名稱
CTE
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/367503.html
標籤：mysql sql sql-server
下一篇：SQL互斥條件