27,579
社区成员
发帖
与我相关
我的任务
分享
--有张表#tb_yskm,这张表就是财政功能科目表
fzdm fzmc
201 mc1
20101 mc2
2010101 mc3
202
..............
#tb_zb,指标情况表,yskmdm就是FZDM,但是此表yskmdm都是明细项。
yskmdm je1 je2 je3
2010101 500.00 120.00 0.00
..................................
#tb_jh,指标情况表,yskmdm就是FZDM,但是此表yskmdm都是明细项。
yskmdm j1 j2 j3
2010101 400.00 20.00 0.00
..................................
--需求:得到含所有FZDM的指标、计划情况表#tb_zb_jh,并且按照FZDM分级汇总
#tb_zb_jh
fzdm je1 je2 je3 j1 j2 j3
201 500.00 120.00 0.00 400.00 20.00 0.00
20101 500.00 120.00 0.00 400.00 20.00 0.00
2010101 500.00 120.00 0.00 400.00 20.00 0.00
........................
--我是这样是写的SQL,但是分级汇总的数据不对啊,求教!
SELECT A.FZDM,
je1=ISNULL(SUM(B.je1),0),
je2=ISNULL(SUM(B.je2),0),
je3=ISNULL(SUM(B.je3),0),
j1=ISNULL(SUM(C.j1),0),
j2=ISNULL(SUM(C.j2),0),
j3=ISNULL(SUM(C.j3),0)
FROM #tb_yskm as A
LEFT JOIN #tb_zb as B
ON B.YSKMDM like A.FZDM+'%'
LEFT JOIN #tb_jh as C
ON C.YSKMDM like A.FZDM+'%'
GROUP BY A.FZDM
)t
select t1.FZDM,t1.je1,t1.je2,t1.je3,t2.j1,t2.j2,t2.j3
from(
SELECT A.FZDM,
je1=ISNULL(SUM(B.je1),0),
je2=ISNULL(SUM(B.je2),0),
je3=ISNULL(SUM(B.je3),0),
FROM #tb_yskm as A
LEFT JOIN #tb_zb as B
ON B.YSKMDM like A.FZDM+'%'
GROUP BY A.FZDM
)t1 inner join(
SELECT A.FZDM,
j1=ISNULL(SUM(C.j1),0),
j2=ISNULL(SUM(C.j2),0),
j3=ISNULL(SUM(C.j3),0)
FROM #tb_yskm as A
LEFT JOIN #tb_jh as C
ON C.YSKMDM like A.FZDM+'%'
GROUP BY A.FZDM
)t2 on t1.FZDM=t2.FZDM
部分实际数据
#tb_zb
2010101 0 0 5840300
2010102 0 0 0
2010104 0 0 1970000
2010105 0 0 200000
2010106 0 0 100000
2010107 0 0 500000
2010108 0 0 1780000
2010199 0 200000 1470000
2010201 0 0 4381100
2010202 0 0 0
2010204 0 0 2250000
2010205 0 0 700000
2010206 0 0 400000
2010299 300000 200000 2650000
#tb_jh
2010101 0 0 5761600
2010102 0 0 0
2010104 0 0 1909400
2010105 0 0 196000
2010106 0 0 100000
2010107 0 0 499000
2010108 0 0 1776000
2010199 0 0 1361600
2010201 0 0 4313000
2010202 0 0 0
2010204 0 0 2198000
2010205 0 0 698000
2010206 0 0 350000
2010299 300000 0 2285000
结果的表#tb_zb_jh
201 2508427500.00 3226754600.00 100816517270.00 2249824500.00 474442000.00 58376468009.39
20101 0.00 1600000.00 94882400.00 0.00 0.00 92828800.00
2010101 0.00 0.00 5840300.00 0.00 0.00 5761600.00
2010102 0.00 0.00 0.00 0.00 0.00 0.00
2010103 0.00 0.00 0.00 0.00 0.00 0.00
2010104 0.00 0.00 1970000.00 0.00 0.00 1909400.00
2010105 0.00 0.00 200000.00 0.00 0.00 196000.00
2010106 0.00 0.00 100000.00 0.00 0.00 100000.00
2010107 0.00 0.00 500000.00 0.00 0.00 499000.00
2010108 0.00 0.00 1780000.00 0.00 0.00 1776000.00
2010109 0.00 0.00 0.00 0.00 0.00 0.00
2010150 0.00 0.00 0.00 0.00 0.00 0.00
2010199 0.00 200000.00 1470000.00 0.00 0.00 1361600.00
20102 1800000.00 1200000.00 62286600.00 1800000.00 0.00 59064000.00
2010201 0.00 0.00 4381100.00 0.00 0.00 4313000.00
2010202 0.00 0.00 0.00 0.00 0.00 0.00
2010203 0.00 0.00 0.00 0.00 0.00 0.00
2010204 0.00 0.00 2250000.00 0.00 0.00 2198000.00
2010205 0.00 0.00 700000.00 0.00 0.00 698000.00
2010206 0.00 0.00 400000.00 0.00 0.00 350000.00
2010250 0.00 0.00 0.00 0.00 0.00 0.00
2010299 300000.00 200000.00 2650000.00 300000.00 0.00 2285000.00
--求助啊!