2、3给一个例子,自己写吧测试数据 DECLARE @a TABLE(No varchar(10),Name varchar(10)) INSERT @a SELECT '101' ,'现金' UNION ALL SELECT '102' ,'银行存款' UNION ALL SELECT '10201','工行' UNION ALL SELECT '10202','建行' UNION ALL SELECT '10203','农行'DECLARE @b TABLE(No varchar(10),[Money] money ) INSERT @b SELECT '101' ,100 UNION ALL SELECT '10201',20 UNION ALL SELECT '10202',120--逐级汇总查询 SELECT a.No,a.Name, [Money]=ISNULL(SUM([Money]),0) FROM @a a LEFT JOIN @b b ON b.No LIKE a.No+'%' GROUP BY a.No,a.Name ORDER BY a.No /*--结果 No Name Money ---------------- ----------------- --------------------- 101 现金 100.0000 102 银行存款 140.0000 10201 工行 20.0000 10202 建行 120.0000 10203 农行 .0000 --*/
DECLARE @a TABLE(No varchar(10),Name varchar(10))
INSERT @a SELECT '101' ,'现金'
UNION ALL SELECT '102' ,'银行存款'
UNION ALL SELECT '10201','工行'
UNION ALL SELECT '10202','建行'
UNION ALL SELECT '10203','农行'DECLARE @b TABLE(No varchar(10),[Money] money )
INSERT @b SELECT '101' ,100
UNION ALL SELECT '10201',20
UNION ALL SELECT '10202',120--逐级汇总查询
SELECT a.No,a.Name,
[Money]=ISNULL(SUM([Money]),0)
FROM @a a
LEFT JOIN @b b ON b.No LIKE a.No+'%'
GROUP BY a.No,a.Name
ORDER BY a.No
/*--结果
No Name Money
---------------- ----------------- ---------------------
101 现金 100.0000
102 银行存款 140.0000
10201 工行 20.0000
10202 建行 120.0000
10203 农行 .0000
--*/