--测试数据
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
--*/