- SQL code
--有张表#tb_yskm，这张表就是财政功能科目表fzdm fzmc201 mc120101 mc22010101 mc3202..............#tb_zb,指标情况表，yskmdm就是FZDM,但是此表yskmdm都是明细项。yskmdm je1 je2 je32010101 500.00 120.00 0.00..................................#tb_jh,指标情况表，yskmdm就是FZDM,但是此表yskmdm都是明细项。yskmdm j1 j2 j32010101 400.00 20.00 0.00..................................--需求：得到含所有FZDM的指标、计划情况表#tb_zb_jh,并且按照FZDM分级汇总#tb_zb_jhfzdm je1 je2 je3 j1 j2 j3201 500.00 120.00 0.00 400.00 20.00 0.0020101 500.00 120.00 0.00 400.00 20.00 0.002010101 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 ALEFT JOIN #tb_zb as BON B.YSKMDM like A.FZDM+'%'LEFT JOIN #tb_jh as CON C.YSKMDM like A.FZDM+'%'GROUP BY A.FZDM)t
------解决方案--------------------
先把后面两个表UNION ALL 然后与第一个表left join
------解决方案--------------------
- SQL code
select t1.FZDM,t1.je1,t1.je2,t1.je3,t2.j1,t2.j2,t2.j3from(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 ALEFT JOIN #tb_zb as BON 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 ALEFT JOIN #tb_jh as CON C.YSKMDM like A.FZDM+'%'GROUP BY A.FZDM)t2 on t1.FZDM=t2.FZDM
------解决方案--------------------
SELECT A.FZDM,
je1=ISNULL((select SUM(B.je1) from #tb_zb as B where a.yskmdm = b.yskmdm),0)
FROM #tb_yskm as A
子查询即可，其它几项类推，呵呵，科目编码怎么是三位呀。