1 IF (0=dbo.EXISTSTABLE('#test')) 2 BEGIN 3 SELECT 4 * INTO #test 5 FROM (SELECT 6 HZDW.F_ID, 7 HZDW.F_CONTACT, 8 HZDW.F_NAME, 9 ISNULL(SUM(LWHT.HTW), 0.0) AS HTWJE, 10 ISNULL(SUM(LWHT.HTZJ), 0.0) AS ZHTJE, 11 CASE 12 WHEN ISNULL(SUM(LWHT.HTZJ), 0.0) = 0.0 THEN 0 ELSE (ISNULL(SUM(LWHT.HTW), 0.0)) * 100 / SUM(LWHT.HTZJ) 13 END HTWBL 14 FROM (SELECT 15 TEMPHT.HTID, 16 TEMPHT.HTZJ, 17 TEMPHT.DFDWID, 18 CASE 19 WHEN LWWGJS.F_ID IS NOT NULL THEN LWWGJS.WGHTW 20 WHEN LWWGJS.F_ID IS NULL THEN LWGCJS.GCHTW ELSE 0.0 21 END AS HTW 22 FROM (SELECT 23 HT.F_ID AS HTID, 24 HT.F_HTZJ AS HTZJ, 25 HT.F_DFDW_ID AS DFDWID 26 FROM T_PM_HT HT 27 INNER JOIN T_ORG_DEPT DEPT 28 ON HT.F_DEPT_ID = DEPT.F_DEPT_ID 29 WHERE HT.F_ENTITY_TYPE = 'Contract.LW.LWFBHT' AND HT.F_EFFECTIVE = 1 AND HT.F_PID = 0) 30 TEMPHT 31 LEFT JOIN (SELECT 32 WGJS.F_ID F_ID, 33 WGJS.F_HT_ID WGJSHTID, 34 ISNULL(WGJSKZ.F_GSSPJHTW, 0.0) WGHTW 35 FROM T_PM_HT_WGJS WGJS 36 LEFT JOIN T_PM_HTLW_WGJSKZ WGJSKZ 37 ON WGJS.F_ID = WGJSKZ.F_ID 38 WHERE WGJS.F_EFFECTIVE = 1 AND WGJS.F_ENTITY_TYPE = 'Contract.LW.LWFBHTWGJS') 39 LWWGJS 40 ON TEMPHT.HTID = LWWGJS.WGJSHTID 41 LEFT JOIN (SELECT 42 GCJS.F_HT_ID GCJSHTID, 43 ISNULL(SUM(GCJSKZ.F_BQGSSPJHTW), 0.0) GCHTW 44 FROM T_PM_HT_GCJS GCJS 45 LEFT JOIN T_PM_HTLW_GCJSKZ GCJSKZ 46 ON GCJS.F_ID = GCJSKZ.F_ID 47 WHERE GCJS.F_EFFECTIVE = 1 AND GCJS.F_ENTITY_TYPE = 'Contract.LW.LWFBHTGCJS' 48 GROUP BY GCJS.F_HT_ID) 49 LWGCJS 50 ON TEMPHT.HTID = LWGCJS.GCJSHTID) 51 LWHT 52 INNER JOIN T_PMC_HZDW HZDW 53 ON LWHT.DFDWID = HZDW.F_ID 54 GROUP BY HZDW.F_ID, 55 HZDW.F_NAME, 56 HZDW.F_CONTACT) 57 AS T_GSQL_CREATE_AS_SELECT; 58 END; 59 SELECT 60 '合计' AS HJ, 61 NULL AS F_ID, 62 NULL AS F_CONTACT, 63 NULL AS F_NAME, 64 ISNULL(SUM(HTWJE), 0.0) AS HTWJE, 65 ISNULL(SUM(ZHTJE), 0.0) AS ZHTJE, 66 CASE 67 WHEN ISNULL(SUM(ZHTJE), 0.0) = 0.0 THEN 0 ELSE (ISNULL(SUM(HTWJE), 0.0)) * 100 / SUM(ZHTJE) 68 END HTWBL 69 FROM #test UNION SELECT 70 NULL AS HJ, 71 F_ID, 72 F_CONTACT, 73 F_NAME, 74 HTWJE, 75 ZHTJE, 76 HTWBL 77 FROM #test 78 WHERE ABS(HTWJE) > 0; 79 DELETE FROM #test; 80 TRUNCATE TABLE #test; 81 DROP TABLE #test;