下面是我創建并在其中插入值的表：
CREATE TABLE Purchases
(PurchaseDate varchar(25),
SoldAmount int)
GO
INSERT INTO Purchases VALUES ('2001-01-28', 325),
('2001-02-28', 245),
('2001-02-28', 587),
('2001-03-28', 312),
('2001-04-28', 325),
('2001-05-28', 675),
('2001-05-28', 228),
('2001-06-28', 109),
('2001-07-28', 905),
('2001-07-28', 207),
('2001-08-28', 503),
('2001-08-28', 102),
('2001-09-28', 504),
('2001-09-28', 103),
('2001-09-28', 542),
('2001-10-28', 915),
('2001-10-28', 755),
('2001-11-28', 385),
('2001-12-28', 285),
('2002-01-28', 492),
('2002-02-28', 286),
('2002-02-28', 664),
('2002-03-28', 883),
('2002-04-28', 673),
('2002-05-28', 200),
('2002-05-28', 421),
('2002-06-28', 642),
('2002-07-28', 325),
('2002-07-28', 789),
('2002-08-28', 432),
('2002-08-28', 432),
('2002-09-28', 886),
('2002-09-28', 310),
('2002-09-28', 970),
('2002-10-28', 297),
('2002-10-28', 301),
('2002-11-28', 570),
('2002-12-28', 921)
GO
現在的問題是： 按月顯示每年的總銷售額（SoldAmount）
這就是我所做的：
SELECT [Month],[2001],[2002]
FROM
(
SELECT DATENAME(M,PurchaseDate) AS [Month],YEAR(PurchaseDate) AS [Year],SoldAmount
FROM Purchases
) AS DataSource
PIVOT
(
SUM(SoldAmount)
FOR [Year] IN ([2001],[2002])
) AS Pivoting
ORDER BY [Month]
我得到以下結果：
雖然我得到了正確的結果，但唯一的問題是盡管添加了 ORDER BY [Month]，但 Month 列并未按升序排序。
如何在資料透視表中按升序對月份進行排序？
uj5u.com熱心網友回復：
您可以將月份數添加到查詢的資料透視部分，然后使用它進行排序：
SELECT [Month], [2001], [2002]
FROM
(
SELECT DATENAME(M, PurchaseDate) AS [Month],
MONTH(PurchaseDate) AS mn,
YEAR(PurchaseDate) AS [Year],
SoldAmount
FROM Purchases
) AS DataSource
PIVOT
(
SUM(SoldAmount)
FOR [Year] IN ([2001],[2002])
) AS Pivoting
ORDER BY mn;
uj5u.com熱心網友回復：
按月排序（'01-' 月 '-2000'）
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/449519.html