--说明：个人学习笔记，实现小计合计显示，分组按BANK_ID+OP_DATE升序排序

复制代码 代码如下:

--测试数据

CREATE TABLE #TB(ID VARCHAR(10),BANK_ID VARCHAR(10),OP_DATE VARCHAR(10),OPERATOR_NO VARCHAR(20),AMT DECIMAL(10,2))

INSERT #TB SELECT '1111','001','20121210',1234567,111.00

UNION ALL SELECT '2222','002','20121210',1234567,222.00

UNION ALL SELECT '3333','001','20121112',1234567,250.00

UNION ALL SELECT '4444','002','20121110',1234567,330.00

UNION ALL SELECT '5555','001','20121210',1234567,300.00

UNION ALL SELECT '6666','002','20121112',1234567,150.00

GO

--查询

SELECT A.ID

,A.BANK_ID

,A.AMT

,B.OP_DATE

,B.OPERATOR_NO

FROM

(

(SELECT

ID=CASE

WHEN GROUPING(BANK_ID)=1 THEN '合计'

WHEN GROUPING(ID)=1 THEN '小计'

ELSE ID END

,BANK_ID

,SUM(AMT) AMT

,ORDER_SIGN1=GROUPING(BANK_ID),ORDER_SIGN2=BANK_ID

,ORDER_SIGN3=GROUPING(ID)

FROM #TB

GROUP BY BANK_ID,ID WITH ROLLUP

HAVING GROUPING(ID)=1

UNION ALL--先得出表的统计，再加上表中的数据

SELECT ID

,BANK_ID

,AMT

,ORDER_SIGN1=0,ORDER_SIGN2=BANK_ID

,ORDER_SIGN3=0

FROM #TB) A

LEFT JOIN--为了显示出OP_DATE、OPERATOR_NO

(SELECT ID

,OP_DATE

,OPERATOR_NO

FROM #TB) B ON A.ID = B.ID

) ORDER BY ORDER_SIGN1,ORDER_SIGN2,ORDER_SIGN3,OP_DATE

GO

--删除测试

DROP TABLE #TB

/*--测试结果

IDBANK_IDAMTOP_DATEOPERATOR_NO

3333001250.00201211121234567

5555001300.00201212101234567

1111001111.00201212101234567

小计001661.00NULLNULL

4444002330.00201211101234567

6666002150.00201211121234567

2222002222.00201212101234567

小计002702.00NULLNULL

合计NULL1363.00NULLNULL

--*/