mysql小计_使用SQL实现小计,合计以及排序_MySQL

bitsCN.com

--说明：个人学习笔记，实现小计合计显示，分组按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)