[SQL Server 2005] 在查詢中建立小計的資料列並排序

在SQL的應用中, 常見到要查出數量與小計的問題.
以下提供一個以 SQL Server 2005 的 ROW_NUMBER() 與 RANK() , 在一次的查詢中將資料查出數量與小計的方式.
不過還是建議在程式中進行這些作業, 以避免當資料量大時, 查詢效能或擴充性不好的問題.

例如如下的資料表 (orderTbl): MemberName (帳號), ItemID (商品 ID), Amount (訂單數量)

MemberName	ItemID	Amount
AAA	111	1
AAA	222	2
BBB	111	3
BBB	333	5
CCC	444	4

想要查出的資料結果如下: 小計數量高的項目在上, 細項中的數量由小到大排序.

BBB	3
BBB	5
BBB小計	8
CCC	4
CCC小計	4
AAA	1
AAA	2
AAA小計	3

查詢句如下:

--建立一個小計的表格, 並使用ROW_NUMBER() 做小計的排序

WITH SubTotalTbl(MemberName,Amount,GroupOrder) AS
(
  SELECT MemberName,sum(Amount) as SubTotal, 
ROW_NUMBER() OVER (order by sum(Amount) desc)
  FROM orderTbl 
  GROUP BY MemberName
)
SELECT *
FROM
(
--將orderTbl與小計表做Join,並將小計的排序值附加上去, 透過RANK()做出群組的效果
SELECT A.MemberName,A.Amount, 
RANK() OVER (PARTITION BY A.MemberName order by A.Amount) RowOrder, B.GroupOrder
FROM orderTbl A LEFT JOIN SubTotalTbl B ON A.MemberName=B.MemberName
UNION ALL
--為了將小計放在群組的最後面, 給一個9999的數字, 此數字應依需要調整
SELECT MemberName+'小計',Amount, 9999, GroupOrder  
FROM SubTotalTbl
) A
--先以群組排序, 再以列數排序
ORDER BY A.GroupOrder,A.RowOrder

查詢的結果將如下所示:

MemberName	Amount	RowOrder	GroupOrder
BBB	3	1	1
BBB	5	2	1
BBB小計	8	9999	1
CCC	4	1	2
CCC小計	4	9999	2
AAA	1	1	3
AAA	2	2	3
AAA小計	3	9999	3

之後透過 GridView 進行 DataBind(), 就能出現一個明細與小計的清單.