临时表创建与使用 

 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;

 

posted @ 2017-07-14 16:37  wuln  阅读(429)  评论(0)    收藏  举报