[SQL]以某個欄位群組N筆數做分頁加總

2013-01-30

7924
0
SQL


NOX varchar(8),
NO_ITEM varchar(2),
QTY int
);

truncate table XX;

insert into XX values('A0001','01',100);
insert into XX values('A0001','02',100);
insert into XX values('A0002','01',110);
insert into XX values('A0002','02',110);
insert into XX values('A0002','03',110);
insert into XX values('A0002','04',110);
insert into XX values('A0002','05',110);
insert into XX values('A0003','01',200);
insert into XX values('A0003','02',200);
insert into XX values('A0003','03',200);
insert into XX values('A0003','04',200);

如果要以NOX欄位群組2筆數分頁加總，產生的結果如下，

一開始想說用NTILE去分群，然後做加總!

但是因為每個NOX的NTILE要依他的資料筆數來分群，

A0001的要分1群，A0002的要分3群，A0003要分2群。


FROM dbo.XX AS A
GROUP BY NOX;

但無法把上面的SQL放到NTILE之中。

後來找到「How to group rows in fixed group size with MSSQL」

，然後直接使用 CEILING(ROW_NUMBER() OVER (PARTITION BY 群組欄位 ORDER BY 群組欄位) /2.0) + SubQuery來處理就可順利解決。如下，


AS
(
SELECT CEILING(row_number() OVER (PARTITION BY NOX ORDER BY NOX) /2.0) AS 'Grouping',
       *
FROM dbo.XX AS X
)
SELECT A.NOX, A.NO_ITEM, A.QTY
, (SELECT SUM(B.QTY) FROM NOXT1 B WHERE A.NOX = B.NOX AND A.Grouping = B.Grouping) AS '分頁小計'
, (SELECT SUM(B.QTY) FROM NOXT1 B WHERE A.NOX = B.NOX AND A.Grouping >= B.Grouping) AS '群組累計'
FROM NOXT1 A;

另外，再加入群組頁數資訊如下，


AS
(
SELECT CEILING(row_number() OVER (PARTITION BY NOX ORDER BY NOX) /2.0) AS 'Grouping',
       *
FROM dbo.XX AS X
)
,NOXT2
AS
(
SELECT SUM(NTILE_CNT) AS SUM_NTILE_CNT 
FROM (
SELECT  A.NOX, CEILING(COUNT(*)/ 2.0) AS NTILE_CNT
FROM dbo.XX AS A
GROUP BY NOX ) Z
)
SELECT A.NOX, A.NO_ITEM, A.QTY
, A.Grouping AS '群組頁數'
, (SELECT SUM(B.QTY) FROM NOXT1 B WHERE A.NOX = B.NOX AND A.Grouping = B.Grouping) AS '分頁小計'
, (SELECT SUM(B.QTY) FROM NOXT1 B WHERE A.NOX = B.NOX AND A.Grouping >= B.Grouping) AS '群組累計'
, (select MAX(B.Grouping) from NOXT1 B WHERE A.NOX = B.NOX  ) '群組總頁'
, (select SUM_NTILE_CNT from NOXT2 C) '總頁數'
FROM NOXT1 A;