这样的查询效果是怎么写的？
业务编号 币别 金额
A0001 RMB 102.00
A0001 RMB 144.00
A0001 RMB 1030.00
A0002 RMB 231.00
A0002 USD 244.00
A0002 USD 123.00
A0003 RMB 111.00
A0003 USD 222.00
A0003 RMB 333.00
A0003 USD 222.00
想要这样的效果
业务编号 币别 金额
A0001 RMB 102.00
A0001 RMB 144.00
A0001 RMB 103.00
小计 349.00
A0002 RMB 231.00
小计 231.00
A0002 USD 244.00
A0002 USD 123.00
小计 367.00
A0003 RMB 111.00
A0003 RMB 333.00
小计 444.00
A0003 USD 222.00
A0003 USD 222.00
小计 444.00
[最优解释]
--> 测试数据：#tb
IF OBJECT_ID('TEMPDB.DBO.#tb') IS NOT NULL DROP TABLE #tb
GO
CREATE TABLE #tb([业务编号] VARCHAR(5),[币别] VARCHAR(3),[金额] NUMERIC(6,2))
INSERT #tb
SELECT 'A0001','RMB',102.00 UNION ALL
SELECT 'A0001','RMB',144.00 UNION ALL
SELECT 'A0001','RMB',103.00 UNION ALL
SELECT 'A0002','RMB',231.00 UNION ALL
SELECT 'A0002','USD',244.00 UNION ALL
SELECT 'A0002','USD',123.00 UNION ALL
SELECT 'A0003','RMB',111.00 UNION ALL
SELECT 'A0003','USD',222.00 UNION ALL
SELECT 'A0003','RMB',333.00 UNION ALL
SELECT 'A0003','USD',222.00
--------------开始查询--------------------------
SELECT [业务编号]=CASE WHEN orderid=2 THEN '小计' ELSE t.[业务编号] END ,[币别],[金额]
FROM
(
SELECT *,orderid=1 FROM #tb
UNION ALL
SELECT [业务编号],[币别],SUM([金额]),orderid=2 FROM #tb GROUP BY [业务编号],[币别]
) t
ORDER BY t.[业务编号],[币别],orderid
----------------结果----------------------------
/*
业务编号币别金额
A0001RMB102.00
A0001RMB144.00
A0001RMB103.00
小计RMB349.00
A0002RMB231.00
小计RMB231.00
A0002USD244.00
A0002USD123.00
小计USD367.00
A0003RMB333.00
A0003RMB111.00
小计RMB444.00
A0003USD222.00
A0003USD222.00
小计USD444.00
*/
select 业务编号, 币别, 金额
from 表
compute sum(金额) by 业务编号, 币别
SELECT [业务编号],币别,sum(金额) FROM #tb
GROUP BY [业务编号],[币别] with rollup
