SQL Server 在SQL中对月份从一月到十二月进行排序

vhmi4jdf 于 2023-01-25 发布在其他

关注(0)|答案(3)|浏览(294)

我想从一月开始到十二月排序，但这里是我的情况：
Result of my query

这是我对上图的查询

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)
但是，我得到了一个错误Error Message
最初，NameMonth 不是表中的列。

非常感谢大家的回答

sql-server

来源：https://stackoverflow.com/questions/70132118/sort-months-from-january-to-december-in-sql

3条答案

按热度按时间

rqqzpn5f1#

你能做到的。

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)

赞(0）回复(0）举报 2023-01-25

2uluyalo2#

首先，使用下面的方法，每个月在一行中显示销售输出。然后，可以使用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

赞(0）回复(0）举报 2023-01-25

wkftcu5l3#

以下查询使用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

参考文献：
DATENAME
CTE

赞(0）回复(0）举报 2023-01-25

我来回答

SQL Server 在SQL中对月份从一月到十二月进行排序

3条答案

相关问题

热门标签

最新问答