SELECT a.name_gov AS 省名 ,
e.suma1 AS 省区市 ,
b.suma1 AS 地市 ,
c.suma1 AS 县区 ,
d.suma1 AS 合计
FROM Work_ReportIncome w
LEFT JOIN ( SELECT name_gov ,
code_gov
FROM bas_gov
WHERE LEN(code_gov) = 2
) a ON a.code_gov = w.code_province
LEFT JOIN ( SELECT code_province ,
SUM(a1) AS suma1
FROM Work_ReportIncome
WHERE LEN(code_gov) = 2
AND code_year = 2011
AND yearType = 2
AND isaudit = 1
GROUP BY code_province
) e ON e.code_province = a.code_gov
LEFT JOIN ( SELECT code_province ,
SUM(a1) AS suma1
FROM Work_ReportIncome
WHERE code_gov IN (
SELECT code_gov
FROM bas_gov
WHERE par_code IN ( SELECT code_gov
FROM bas_gov
WHERE LEN(code_gov) = 2 ) )
AND code_year = 2011
AND yearType = 2
AND isaudit = 1
GROUP BY code_province
) b ON b.code_province = a.code_gov
LEFT JOIN ( SELECT code_province ,
SUM(a1) AS suma1
FROM Work_ReportIncome
WHERE LEN(code_gov) = 6
AND code_year = 2011
AND yearType = 2
AND isaudit = 1
GROUP BY code_province
) c ON c.code_province = A.code_gov
LEFT JOIN ( SELECT code_province ,
SUM(a1) AS suma1
FROM Work_ReportIncome
WHERE 1 = 1
AND code_year = 2011
AND yearType = 2
AND isaudit = 1
GROUP BY code_province
) d ON d.code_province = a.code_gov
WHERE w.code_year = '2011'
AND w.yearType = '2'
AND w.isaudit = '1'
GROUP BY a.name_gov ,
e.suma1 ,
b.suma1 ,
C.suma1 ,
d.suma1
UNION ALL
SELECT '合计' ,
SUM(a.省区市) ,
SUM(a.地市) ,
SUM(a.县区) ,
SUM(a.合计)
FROM ( SELECT a.name_gov AS 省名 ,
e.suma1 AS 省区市 ,
b.suma1 AS 地市 ,
c.suma1 AS 县区 ,
d.suma1 AS 合计
FROM Work_ReportIncome w
LEFT JOIN ( SELECT name_gov ,
code_gov
FROM bas_gov
WHERE LEN(code_gov) = 2
) a ON a.code_gov = w.code_province
LEFT JOIN ( SELECT code_province ,
SUM(a1) AS suma1
FROM Work_ReportIncome
WHERE LEN(code_gov) = 2
AND code_year = 2011
AND yearType = 2
AND isaudit = 1
GROUP BY code_province
) e ON e.code_province = a.code_gov
LEFT JOIN ( SELECT code_province ,
SUM(a1) AS suma1
FROM Work_ReportIncome
WHERE code_gov IN (
SELECT code_gov
FROM bas_gov
WHERE par_code IN (
SELECT code_gov
FROM bas_gov
WHERE LEN(code_gov) = 2 ) )
AND code_year = 2011
AND yearType = 2
AND isaudit = 1
GROUP BY code_province
) b ON b.code_province = a.code_gov
LEFT JOIN ( SELECT code_province ,
SUM(a1) AS suma1
FROM Work_ReportIncome
WHERE LEN(code_gov) = 6
AND code_year = 2011
AND yearType = 2
AND isaudit = 1
GROUP BY code_province
) c ON c.code_province = A.code_gov
LEFT JOIN ( SELECT code_province ,
SUM(a1) AS suma1
FROM Work_ReportIncome
WHERE 1 = 1
AND code_year = 2011
AND yearType = 2
AND isaudit = 1
GROUP BY code_province
) d ON d.code_province = a.code_gov
WHERE w.code_year = '2011'
AND w.yearType = '2'
AND w.isaudit = '1'
GROUP BY a.name_gov ,
e.suma1 ,
b.suma1 ,
C.suma1 ,
d.suma1
) AS a