拼组PROC

最新推荐文章于 2025-08-19 15:12:31 发布
原创最新推荐文章于 2025-08-19 15:12:31 发布 · 610 阅读
0 ·
CC 4.0 BY-SA版权
文章标签：
#sql
sql 专栏收录该内容
2 篇文章
订阅专栏
CREATE PROC proc_zjgl_sbtj
    (
      @nf VARCHAR(4) ,
      @xmid VARCHAR(4) ,
      @sbxmid VARCHAR(4) ,
      @tableName VARCHAR(20) ,
      @tableBHColumn VARCHAR(20)
    )
AS 
    BEGIN
        DECLARE @sql VARCHAR(8000);
        SET @sql = '
SELECT f.YLJGBH,f.YLJGMC,f.SL ,f.SHTGS,TGL,e.BHFCL FROM 
(SELECT f.SL,f.YLJGBH,f.YLJGMC,e.SHTGS, CASE WHEN f.SL = 0 then 0 else CONVERT(DECIMAL(5,4),CONVERT(DECIMAL,e.SHTGS)/CONVERT(DECIMAL,f.SL)) end AS TGL FROM (
--dt1 资金申报量
--市级
SELECT SUM(ISNULL(sl,0)) AS SL,''0'' as yljgbh , ''市级'' as yljgmc FROM(
SELECT SL,Y.YLJGBH,YLJGMC FROM SKJBXX_YLJG AS Y LEFT JOIN (
SELECT COUNT(1) AS SL,syljgbh FROM (
SELECT *,SUBSTRING(' + @tableBHColumn
            + ',1,4)  AS syljgbh FROM ( select * from ' + @tableName
            + ') as g ) AS a 
WHERE A.SBSJID IN (SELECT DISTINCT BH FROM ZJGL_SBXMSZ a LEFT JOIN ( select * from '
            + @tableName + ') as b ON a.BH = b.SBSJID WHERE a.xmid= '''
            + CAST(@xmid AS VARCHAR(4)) + ''' AND nf ='''
            + CAST(@nf AS VARCHAR(4)) + ''' AND bh ='''
            + CAST(@sbxmid AS VARCHAR(4))
            + '''
)
GROUP BY a.syljgbh ) AS B ON Y.YLJGBH=B.syljgbh WHERE LEN(Y.YLJGBH)<5 AND y.YLJGBH > ''0107'''
            + ')AS D 
UNION
--区旗县
SELECT ISNULL(SL,0) SL,YLJGBH,YLJGMC FROM (
SELECT SL,Y.YLJGBH,YLJGMC FROM SKJBXX_YLJG AS Y LEFT JOIN (
SELECT COUNT(1) AS SL,syljgbh FROM (
SELECT *,SUBSTRING(' + @tableBHColumn
            + ',1,4)  AS syljgbh FROM ( select * from ' + @tableName
            + ') as g ) AS a 
WHERE A.SBSJID IN (SELECT DISTINCT BH FROM ZJGL_SBXMSZ a LEFT JOIN ( select * from '
            + @tableName + ') as b ON a.BH = b.SBSJID WHERE a.xmid= '''
            + CAST(@xmid AS VARCHAR(4)) + ''' AND nf ='''
            + CAST(@nf AS VARCHAR(4)) + ''' AND bh ='''
            + CAST(@sbxmid AS VARCHAR(4))
            + '''
)
GROUP BY a.syljgbh ) AS B ON Y.YLJGBH=B.syljgbh WHERE LEN(Y.YLJGBH)<5 AND y.YLJGBH <= ''0107'''
            + ' AND y.YLJGBH != ''01''' + ')AS D 
UNION
--合计
SELECT SUM(ISNULL(sl,0)) SL, ''9999'' as yljgbh , ''合计'' as yljgmc FROM(
SELECT SL,Y.YLJGBH,YLJGMC FROM SKJBXX_YLJG AS Y LEFT JOIN (
SELECT COUNT(1) AS SL,syljgbh FROM (
SELECT *,SUBSTRING(' + @tableBHColumn
            + ',1,4)  AS syljgbh FROM ( select * from ' + @tableName
            + ') as g ) AS a 
WHERE A.SBSJID IN (SELECT DISTINCT BH FROM ZJGL_SBXMSZ a LEFT JOIN ( select * from '
            + @tableName + ') as b ON a.BH = b.SBSJID where a.xmid= '''
            + CAST(@xmid AS VARCHAR(4)) + ''' AND nf ='''
            + CAST(@nf AS VARCHAR(4)) + ''' AND bh ='''
            + CAST(@sbxmid AS VARCHAR(4)) + '''
)
GROUP BY a.syljgbh ) AS B ON Y.YLJGBH=B.syljgbh WHERE LEN(Y.YLJGBH)<5) AS d
) AS f LEFT JOIN
(
--dt2 审核通过量
--市级
SELECT ''0'' as yljgbh , ''市级'' as yljgmc,SUM(ISNULL(d.shtgs,0))SHTGS FROM(
SELECT Y.YLJGBH,YLJGMC,ISNULL(SHTGS,0)SHTGS FROM SKJBXX_YLJG AS Y LEFT JOIN (
SELECT syljgbh ,COUNT(1) AS SHTGS FROM (
SELECT *,SUBSTRING(' + @tableBHColumn
            + ',1,4)  AS syljgbh FROM ( select * from ' + @tableName
            + ') as g WHERE ZT = ''7'''
            + ') AS a
WHERE A.SBSJID IN (SELECT DISTINCT BH FROM ZJGL_SBXMSZ a LEFT JOIN ( select * from '
            + @tableName + ') as b ON a.BH = b.SBSJID WHERE a.xmid= '''
            + CAST(@xmid AS VARCHAR(4)) + ''' AND nf ='''
            + CAST(@nf AS VARCHAR(4)) + ''' AND bh ='''
            + CAST(@sbxmid AS VARCHAR(4))
            + '''
)
GROUP BY a.syljgbh,a.ZT) AS B ON Y.YLJGBH=B.syljgbh WHERE LEN(Y.YLJGBH)< 5 AND y.YLJGBH > ''0107'''
            + ' AND y.YLJGBH != ''01''' + ')AS d
UNION
--区旗县
SELECT Y.YLJGBH,YLJGMC,ISNULL(SHTGS,0)SHTGS FROM SKJBXX_YLJG AS Y LEFT JOIN (
SELECT syljgbh ,COUNT(1) AS SHTGS FROM (
SELECT *,SUBSTRING(' + @tableBHColumn
            + ',1,4)  AS syljgbh FROM ( select * from ' + @tableName
            + ') as g WHERE ZT = ''7'''
            + ') AS a
WHERE A.SBSJID IN (SELECT DISTINCT BH FROM ZJGL_SBXMSZ a LEFT JOIN ( select * from '
            + @tableName + ') as b ON a.BH = b.SBSJID WHERE a.xmid= '''
            + CAST(@xmid AS VARCHAR(4)) + ''' AND nf ='''
            + CAST(@nf AS VARCHAR(4)) + ''' AND bh ='''
            + CAST(@sbxmid AS VARCHAR(4))
            + '''
)
GROUP BY a.syljgbh,a.ZT) AS B ON Y.YLJGBH=B.syljgbh WHERE LEN(Y.YLJGBH)< 5 AND y.YLJGBH <= ''0107'''
            + 'AND y.YLJGBH != ''01''' + '
UNION
--合计
SELECT ''9999'' as yljgbh , ''合计'' as yljgmc,SUM(ISNULL(d.shtgs,0))SHTGS FROM(
SELECT Y.YLJGBH,YLJGMC,ISNULL(SHTGS,0)SHTGS FROM SKJBXX_YLJG AS Y LEFT JOIN (
SELECT syljgbh ,COUNT(1) AS SHTGS FROM (
SELECT *,SUBSTRING(' + @tableBHColumn
            + ',1,4)  AS syljgbh FROM ( select * from ' + @tableName
            + ') as g WHERE ZT = ''7'''
            + ') AS a
WHERE A.SBSJID IN (SELECT DISTINCT BH FROM ZJGL_SBXMSZ a LEFT JOIN ( select * from '
            + @tableName + ') as b ON a.BH = b.SBSJID WHERE a.xmid= '''
            + CAST(@xmid AS VARCHAR(4)) + ''' AND nf ='''
            + CAST(@nf AS VARCHAR(4)) + ''' AND bh ='''
            + CAST(@sbxmid AS VARCHAR(4))
            + '''
)
GROUP BY a.syljgbh,a.ZT) AS B ON Y.YLJGBH=B.syljgbh WHERE LEN(Y.YLJGBH)< 5)AS d
) AS e ON f.YLJGBH = e.YLJGBH) AS f LEFT join
(
--dt3 通过率 dt2/dt1
--dt4 驳回封存量
--旗县区
SELECT Y.YLJGBH,YLJGMC,ISNULL(BHFCL,0)BHFCL FROM SKJBXX_YLJG AS Y LEFT JOIN (
SELECT syljgbh ,COUNT(1) AS BHFCL FROM (
SELECT *,SUBSTRING(' + @tableBHColumn
            + ',1,4)  AS syljgbh FROM ( select * from ' + @tableName
            + ') as g WHERE ZT = ''9'''
            + ') AS a
WHERE A.SBSJID IN (SELECT DISTINCT BH FROM ZJGL_SBXMSZ a LEFT JOIN ( select * from '
            + @tableName + ') as b ON a.BH = b.SBSJID WHERE a.xmid= '''
            + CAST(@xmid AS VARCHAR(4)) + ''' AND nf ='''
            + CAST(@nf AS VARCHAR(4)) + ''' AND bh ='''
            + CAST(@sbxmid AS VARCHAR(4))
            + '''
)
GROUP BY a.syljgbh,a.ZT) AS B ON Y.YLJGBH=B.syljgbh WHERE LEN(Y.YLJGBH)< 5 AND y.YLJGBH <= ''0107'''
            + ' AND y.YLJGBH != ''01'''
            + '
UNION
-- 市级
SELECT ''0'' as yljgbh , ''市级'' as yljgmc,SUM(ISNULL(d.BHFCL,0)) AS BHFCL FROM(
SELECT Y.YLJGBH,YLJGMC,ISNULL(BHFCL,0)BHFCL FROM SKJBXX_YLJG AS Y LEFT JOIN (
SELECT syljgbh ,COUNT(1) AS BHFCL FROM (
SELECT *,SUBSTRING(' + @tableBHColumn
            + ',1,4)  AS syljgbh FROM ( select * from ' + @tableName
            + ') as g WHERE ZT = ''9'''
            + ') AS a
WHERE A.SBSJID IN (SELECT DISTINCT BH FROM ZJGL_SBXMSZ a LEFT JOIN ( select * from '
            + @tableName + ') as b ON a.BH = b.SBSJID WHERE a.xmid= '''
            + CAST(@xmid AS VARCHAR(4)) + ''' AND nf ='''
            + CAST(@nf AS VARCHAR(4)) + ''' AND bh ='''
            + CAST(@sbxmid AS VARCHAR(4))
            + '''
)
GROUP BY a.syljgbh,a.ZT) AS B ON Y.YLJGBH=B.syljgbh WHERE LEN(Y.YLJGBH)< 5 AND y.YLJGBH > ''0107'''
            + ' AND y.YLJGBH != ''01'''
            + ') AS d
UNION
--合计
SELECT ''9999'' as yljgbh , ''合计'' as yljgmc,SUM(ISNULL(d.BHFCL,0)) AS BHFCL FROM(
SELECT Y.YLJGBH,YLJGMC,ISNULL(BHFCL,0)BHFCL FROM SKJBXX_YLJG AS Y LEFT JOIN (
SELECT syljgbh ,COUNT(1) AS BHFCL FROM (
SELECT *,SUBSTRING(' + @tableBHColumn
            + ',1,4)  AS syljgbh FROM ( select * from ' + @tableName
            + ') as g WHERE ZT = ''9'''
            + ') AS a
WHERE A.SBSJID IN (SELECT DISTINCT BH FROM ZJGL_SBXMSZ a LEFT JOIN ( select * from '
            + @tableName + ') as b ON a.BH = b.SBSJID WHERE a.xmid= '''
            + CAST(@xmid AS VARCHAR(4)) + ''' AND nf ='''
            + CAST(@nf AS VARCHAR(4)) + ''' AND bh ='''
            + CAST(@sbxmid AS VARCHAR(4))
            + '''
)
GROUP BY a.syljgbh,a.ZT) AS B ON Y.YLJGBH=B.syljgbh WHERE LEN(Y.YLJGBH)< 5) AS d)AS e
ON f.yljgbh = e.yljgbh'
        PRINT ( @sql )
        EXEC(@sql)

        SET @sql = '
SELECT f.YLJGBH, f.YLJGMC ,F.BHFCBL , f.YBKJE,f.SJBFJE,ISNULL(e.ssje,0) SSJE FROM (
SELECT f.YLJGBH, f.YLJGMC, f.SL, F.BHFCL ,F.BHFCBL , f.YBKJE,ISNULL(e.SJBFJE,0) SJBFJE FROM (
SELECT f.YLJGBH, f.YLJGMC, f.SL, F.BHFCL ,F.BHFCBL , e.YBKJE FROM(
--dt5 驳回封存率 dt4/dt1
SELECT f.YLJGBH, f.YLJGMC, f.SL, e.BHFCL ,CASE WHEN f.SL = 0 then 0 else CONVERT(DECIMAL(5,4),CONVERT(DECIMAL,e.BHFCL)/CONVERT(DECIMAL,f.SL)) end AS BHFCBL FROM (
--dt1
--市级
SELECT SUM(ISNULL(sl,0)) AS SL,''0'' as yljgbh , ''市级'' as yljgmc FROM(
SELECT SL,Y.YLJGBH,YLJGMC FROM SKJBXX_YLJG AS Y LEFT JOIN (
SELECT COUNT(1) AS SL,syljgbh FROM (
SELECT *,SUBSTRING(' + @tableBHColumn
            + ',1,4)  AS syljgbh FROM ( select * from ' + @tableName
            + ') as g ) AS a 
WHERE A.SBSJID IN (SELECT DISTINCT BH FROM ZJGL_SBXMSZ a LEFT JOIN ( select * from '
            + @tableName + ') as b ON a.BH = b.SBSJID WHERE a.xmid= '''
            + CAST(@xmid AS VARCHAR(4)) + ''' AND nf ='''
            + CAST(@nf AS VARCHAR(4)) + ''' AND bh ='''
            + CAST(@sbxmid AS VARCHAR(4))
            + '''
)
GROUP BY a.syljgbh ) AS B ON Y.YLJGBH=B.syljgbh WHERE LEN(Y.YLJGBH)<5 AND y.YLJGBH > ''0107'''
            + ')AS D 
UNION
--区旗县
SELECT ISNULL(SL,0) SL,YLJGBH,YLJGMC FROM (
SELECT SL,Y.YLJGBH,YLJGMC FROM SKJBXX_YLJG AS Y LEFT JOIN (
SELECT COUNT(1) AS SL,syljgbh FROM (
SELECT *,SUBSTRING(' + @tableBHColumn
            + ',1,4)  AS syljgbh FROM ( select * from ' + @tableName
            + ') as g ) AS a 
WHERE A.SBSJID IN (SELECT DISTINCT BH FROM ZJGL_SBXMSZ a LEFT JOIN ( select * from '
            + @tableName + ') as b ON a.BH = b.SBSJID WHERE a.xmid= '''
            + CAST(@xmid AS VARCHAR(4)) + ''' AND nf ='''
            + CAST(@nf AS VARCHAR(4)) + ''' AND bh ='''
            + CAST(@sbxmid AS VARCHAR(4))
            + '''
)
GROUP BY a.syljgbh ) AS B ON Y.YLJGBH=B.syljgbh WHERE LEN(Y.YLJGBH)<5 AND y.YLJGBH <= ''0107'''
            + ' AND y.YLJGBH != ''01''' + ')AS D 
UNION
--合计
SELECT SUM(ISNULL(sl,0)) SL,''9999'' as yljgbh , ''合计'' as yljgmc FROM(
SELECT SL,Y.YLJGBH,YLJGMC FROM SKJBXX_YLJG AS Y LEFT JOIN (
SELECT COUNT(1) AS SL,syljgbh FROM (
SELECT *,SUBSTRING(' + @tableBHColumn
            + ',1,4)  AS syljgbh FROM ( select * from ' + @tableName
            + ') as g ) AS a 
WHERE A.SBSJID IN (SELECT DISTINCT BH FROM ZJGL_SBXMSZ a LEFT JOIN ( select * from '
            + @tableName + ') as b ON a.BH = b.SBSJID WHERE a.xmid= '''
            + CAST(@xmid AS VARCHAR(4)) + ''' AND nf ='''
            + CAST(@nf AS VARCHAR(4)) + ''' AND bh ='''
            + CAST(@sbxmid AS VARCHAR(4)) + '''
)
GROUP BY a.syljgbh ) AS B ON Y.YLJGBH=B.syljgbh WHERE LEN(Y.YLJGBH)<5) AS d
) AS f LEFT JOIN
(
--区旗县 驳回封存量
SELECT Y.YLJGBH,YLJGMC,ISNULL(BHFCL,0)BHFCL FROM SKJBXX_YLJG AS Y LEFT JOIN (
SELECT syljgbh ,COUNT(1) AS BHFCL FROM (
SELECT *,SUBSTRING(' + @tableBHColumn
            + ',1,4)  AS syljgbh FROM ( select * from ' + @tableName
            + ') as g WHERE ZT = ''9'''
            + ') AS a
WHERE A.SBSJID IN (SELECT DISTINCT BH FROM ZJGL_SBXMSZ a LEFT JOIN ( select * from '
            + @tableName + ') as b ON a.BH = b.SBSJID WHERE a.xmid= '''
            + CAST(@xmid AS VARCHAR(4)) + ''' AND nf ='''
            + CAST(@nf AS VARCHAR(4)) + ''' AND bh ='''
            + CAST(@sbxmid AS VARCHAR(4))
            + '''
)
GROUP BY a.syljgbh,a.ZT) AS B ON Y.YLJGBH=B.syljgbh WHERE LEN(Y.YLJGBH)< 5 AND y.YLJGBH <= ''0107'''
            + ' AND y.YLJGBH != ''01'''
            + '
UNION
-- 市级 驳回封存量
SELECT ''0'' as yljgbh , ''市级'' as yljgmc,SUM(ISNULL(d.BHFCL,0)) AS BHFCL FROM(
SELECT Y.YLJGBH,YLJGMC,ISNULL(BHFCL,0)BHFCL FROM SKJBXX_YLJG AS Y LEFT JOIN (
SELECT syljgbh ,COUNT(1) AS BHFCL FROM (
SELECT *,SUBSTRING(' + @tableBHColumn
            + ',1,4)  AS syljgbh FROM ( select * from ' + @tableName
            + ') as g WHERE ZT = ''9'''
            + ') AS a
WHERE A.SBSJID IN (SELECT DISTINCT BH FROM ZJGL_SBXMSZ a LEFT JOIN ( select * from '
            + @tableName + ') as b ON a.BH = b.SBSJID WHERE a.xmid= '''
            + CAST(@xmid AS VARCHAR(4)) + ''' AND nf ='''
            + CAST(@nf AS VARCHAR(4)) + ''' AND bh ='''
            + CAST(@sbxmid AS VARCHAR(4))
            + '''
)
GROUP BY a.syljgbh,a.ZT) AS B ON Y.YLJGBH=B.syljgbh WHERE LEN(Y.YLJGBH)< 5 AND y.YLJGBH > ''0107'''
            + ' AND y.YLJGBH != ''01'''
            + ') AS d
UNION
--合计 驳回封存量
SELECT ''9999'' as yljgbh , ''合计'' as yljgmc,SUM(ISNULL(d.BHFCL,0)) AS BHFCL FROM(
SELECT Y.YLJGBH,YLJGMC,ISNULL(BHFCL,0)BHFCL FROM SKJBXX_YLJG AS Y LEFT JOIN (
SELECT syljgbh ,COUNT(1) AS BHFCL FROM (
SELECT *,SUBSTRING(' + @tableBHColumn
            + ',1,4)  AS syljgbh FROM ( select * from ' + @tableName
            + ') as g WHERE ZT = ''9'''
            + ') AS a
WHERE A.SBSJID IN (SELECT DISTINCT BH FROM ZJGL_SBXMSZ a LEFT JOIN ( select * from '
            + @tableName + ') as b ON a.BH = b.SBSJID WHERE a.xmid= '''
            + CAST(@xmid AS VARCHAR(4)) + ''' AND nf ='''
            + CAST(@nf AS VARCHAR(4)) + ''' AND bh ='''
            + CAST(@sbxmid AS VARCHAR(4))
            + '''
)
GROUP BY a.syljgbh,a.ZT) AS B ON Y.YLJGBH=B.syljgbh WHERE LEN(Y.YLJGBH)< 5) AS d)AS e
ON f.YLJGBH = e.YLJGBH) AS f
LEFT JOIN (
--dt6 应拨款金额
--应拨款金额 审核通过量*拨付标准 旗县区
SELECT DISTINCT f.YLJGBH ,f.YLJGMC,ISNULL(e.XMQXBFBZ*f.SHTGS,0) YBKJE FROM(
SELECT Y.YLJGBH,YLJGMC,ISNULL(SHTGS,0)SHTGS FROM SKJBXX_YLJG AS Y LEFT JOIN (
SELECT syljgbh ,COUNT(1) AS SHTGS FROM (
SELECT *,SUBSTRING(' + @tableBHColumn
            + ',1,4)  AS syljgbh FROM ( select * from ' + @tableName
            + ') as g WHERE ZT = ''7'''
            + ') AS a
WHERE A.SBSJID IN (SELECT DISTINCT BH FROM ZJGL_SBXMSZ a LEFT JOIN ( select * from '
            + @tableName + ') as b ON a.BH = b.SBSJID WHERE a.xmid= '''
            + CAST(@xmid AS VARCHAR(4)) + ''' AND nf ='''
            + CAST(@nf AS VARCHAR(4)) + ''' AND bh ='''
            + CAST(@sbxmid AS VARCHAR(4))
            + '''
)
GROUP BY a.syljgbh,a.ZT) AS B ON Y.YLJGBH=B.syljgbh WHERE LEN(Y.YLJGBH)< 5 AND y.YLJGBH <= ''0107'''
            + ' AND y.YLJGBH != ''01''' + '
) AS f
LEFT JOIN 
(SELECT YLJGBH , XMQXBFBZ FROM  ZJGL_SBXMSZ_MX WHERE xmid='''
            + CAST(@xmid AS VARCHAR(4)) + ''' AND BH = '''
            + CAST(@sbxmid AS VARCHAR(4))
            + ''' AND LEN(YLJGBH) = 4 AND YLJGBH <= ''0107'''
            + ') AS e
ON f.YLJGBH = e.YLJGBH
UNION
--市级
SELECT ''0'' as yljgbh , ''市级'' as yljgmc,ISNULL(SUM(ISNULL(YBKJE,0)),0) YBKJE FROM
(SELECT DISTINCT e.YLJGBH ,f.YLJGMC,e.XMQXBFBZ*SHTGS YBKJE FROM(
SELECT Y.YLJGBH,YLJGMC,ISNULL(SHTGS,0)SHTGS FROM SKJBXX_YLJG AS Y LEFT JOIN (
SELECT syljgbh ,COUNT(1) AS SHTGS FROM (
SELECT *,SUBSTRING(' + @tableBHColumn
            + ',1,4)  AS syljgbh FROM ( select * from ' + @tableName
            + ') as g WHERE ZT = ''7'''
            + ') AS a
WHERE A.SBSJID IN (SELECT DISTINCT BH FROM ZJGL_SBXMSZ a LEFT JOIN ( select * from '
            + @tableName + ') as b ON a.BH = b.SBSJID WHERE a.xmid= '''
            + CAST(@xmid AS VARCHAR(4)) + ''' AND nf ='''
            + CAST(@nf AS VARCHAR(4)) + ''' AND bh ='''
            + CAST(@sbxmid AS VARCHAR(4))
            + '''
)
GROUP BY a.syljgbh,a.ZT) AS B ON Y.YLJGBH=B.syljgbh WHERE LEN(Y.YLJGBH)< 5 AND y.YLJGBH > ''0107'''
            + ' AND y.YLJGBH != ''01''' + '
) AS f
LEFT JOIN 
(SELECT YLJGBH , XMQXBFBZ FROM  ZJGL_SBXMSZ_MX WHERE xmid='''
            + CAST(@xmid AS VARCHAR(4)) + ''' AND BH = '''
            + CAST(@sbxmid AS VARCHAR(4))
            + ''' AND LEN(YLJGBH) = 4 AND YLJGBH > ''0107'''
            + ') AS e
ON f.YLJGBH = e.YLJGBH)AS d
UNION
--合计
SELECT ''9999'' as yljgbh , ''合计'' as yljgmc,ISNULL(SUM(ISNULL(YBKJE,0)),0) YBKJE FROM
(SELECT DISTINCT e.YLJGBH ,f.YLJGMC,e.XMQXBFBZ*SHTGS YBKJE FROM(
SELECT Y.YLJGBH,YLJGMC,ISNULL(SHTGS,0)SHTGS FROM SKJBXX_YLJG AS Y LEFT JOIN (
SELECT syljgbh ,COUNT(1) AS SHTGS FROM (
SELECT *,SUBSTRING(' + @tableBHColumn
            + ',1,4)  AS syljgbh FROM ( select * from ' + @tableName
            + ') as g WHERE ZT = ''7'''
            + ') AS a
WHERE A.SBSJID IN (SELECT DISTINCT BH FROM ZJGL_SBXMSZ a LEFT JOIN ( select * from '
            + @tableName + ') as b ON a.BH = b.SBSJID WHERE a.xmid= '''
            + CAST(@xmid AS VARCHAR(4)) + ''' AND nf ='''
            + CAST(@nf AS VARCHAR(4)) + ''' AND bh ='''
            + CAST(@sbxmid AS VARCHAR(4))
            + '''
)
GROUP BY a.syljgbh,a.ZT) AS B ON Y.YLJGBH=B.syljgbh WHERE LEN(Y.YLJGBH)< 5 AND y.YLJGBH != ''01'''
            + '
) AS f
LEFT JOIN 
(SELECT YLJGBH , XMQXBFBZ FROM  ZJGL_SBXMSZ_MX WHERE xmid='''
            + CAST(@xmid AS VARCHAR(4)) + ''' AND BH = '''
            + CAST(@sbxmid AS VARCHAR(4)) + ''' AND LEN(YLJGBH) = 4) AS e
ON f.YLJGBH = e.YLJGBH)AS d)AS e ON f.yljgbh = e.yljgbh
) AS f LEFT JOIN (
---实拨金额 dt7 旗县区
SELECT a.YLJGBH , a.YLJGMC, SJBFJE FROM dbo.SKJBXX_YLJG a left JOIN 
zjgl_sb b ON a.YLJGBH = b.SBJGBH WHERE len(sbjgbh) = 4 AND SBJGBH <= ''0107'''
            + ' AND SBJGBH != ''01''' + ' AND sbxmid = '''
            + CAST(@sbxmid AS VARCHAR(4)) + '''
UNION
---市级
SELECT ''0'' as yljgbh , ''市级'' as yljgmc,ISNULL(SUM(SJBFJE),0) SJBFJE FROM (
SELECT a.YLJGBH , a.YLJGMC, SJBFJE FROM dbo.SKJBXX_YLJG a left JOIN 
zjgl_sb b ON a.YLJGBH = b.SBJGBH WHERE len(sbjgbh) = 4 AND SBJGBH > ''0107'''
            + ' AND sbxmid = ''' + CAST(@sbxmid AS VARCHAR(4))
            + ''') AS d
UNION
---合计
SELECT ''9999'' as yljgbh , ''合计'' as yljgmc,ISNULL(SUM(SJBFJE),0) SJBFJE FROM (
SELECT a.YLJGBH , a.YLJGMC, SJBFJE FROM dbo.SKJBXX_YLJG a left JOIN 
zjgl_sb b ON a.YLJGBH = b.SBJGBH WHERE len(sbjgbh) = 4 AND sbxmid = '''
            + CAST(@sbxmid AS VARCHAR(4)) + ''') AS d
) AS e ON f.yljgbh = e.yljgbh )AS f
LEFT JOIN (
---实收金额 dt8 旗县区
SELECT a.YLJGBH , a.YLJGMC, SJBFJE SSJE FROM dbo.SKJBXX_YLJG a left JOIN 
zjgl_hz b ON a.YLJGBH = b.HZJGID WHERE len(HZJGID) = 4 AND HZJGID <= ''0107'''
            + ' AND HZJGID != ''01''' + ' AND sbid = '''
            + CAST(@sbxmid AS VARCHAR(4)) + '''
UNION
---市级
SELECT ''0'' as yljgbh , ''市级'' as yljgmc,ISNULL(SUM(SJBFJE),0) SSJE FROM (
SELECT a.YLJGBH , a.YLJGMC, SJBFJE FROM dbo.SKJBXX_YLJG a left JOIN 
zjgl_hz b ON a.YLJGBH = b.HZJGID WHERE len(HZJGID) = 4 AND HZJGID > ''0107'''
            + ' AND sbid = ''' + CAST(@sbxmid AS VARCHAR(4)) + ''') AS d
UNION
---合计
SELECT ''9999'' as yljgbh , ''合计'' as yljgmc,ISNULL(SUM(SJBFJE),0) SSJE FROM (
SELECT a.YLJGBH , a.YLJGMC, SJBFJE FROM dbo.SKJBXX_YLJG a left JOIN 
zjgl_hz b ON a.YLJGBH = b.HZJGID WHERE len(HZJGID) = 4 AND sbid = '''
            + CAST(@sbxmid AS VARCHAR(4)) + ''') AS d)
AS e ON f.yljgbh = e.yljgbh
'
        PRINT ( @sql );
        EXEC(@sql);
    END