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;
