一个超长SQL语句的规范书写格式范例

1. select f.*, g.trueworkload  
2.   from (select a.username, d.batchcode, c.workflowname, count(*) workload  
3.           from t_systemuserinfo a,  
4.                t_genworkflow c,  
5.                t_batch d,  
6.                t_companyinfo e,  
7.                (select distinct batchid,  
8.                                 companyid,  
9.                                 userid,  
10.                                 direction,  
11.                                 fromwkfid,  
12.                                 towkfid  
13.                   from dbo.T_WorkflowLog  
14.                  where dotime between '2008-09-02' and '2008-09-03') as b  
15.          where a.systemuserid = b.userid  
16.            and b.fromwkfid = c.genworkflowid  
17.            and b.batchid = d.batchid  
18.            and b.companyid = e.companyid  
19.          group by a.username, c.workflowname, d.batchcode) f,  
20.        (select a.username,  
21.                d.batchcode,  
22.                c.workflowname,  
23.                count(*) trueworkload  
24.           from t_systemuserinfo a,  
25.                t_genworkflow c,  
26.                t_batch d,  
27.                t_companyinfo e,  
28.                (select distinct batchid,  
29.                                 companyid,  
30.                                 userid,  
31.                                 direction,  
32.                                 fromwkfid,  
33.                                 towkfid  
34.                   from dbo.T_WorkflowLog  
35.                  where dotime between '2008-09-02' and '2008-09-03') as b  
36.          where a.systemuserid = b.userid  
37.            and b.fromwkfid = c.genworkflowid  
38.            and b.batchid = d.batchid  
39.            and b.companyid = e.companyid  
40.            and e.datastatus = 1  
41.            and e.companyid not in  
42.                (select h.companyid  
43.                   from t_companyinfotmp h  
44.                  where h.batchid = b.batchid)  
45.          group by a.username, c.workflowname, d.batchcode, e.datastatus) g  
46.  where f.batchcode = g.batchcode  
47.    and f.workflowname = g.workflowname  
48.    and f.username = g.username  
49. union  
50. select f.*, g.trueworkload  
51.   from (select a.username, d.batchcode, c.workflowname, count(*) workload  
52.           from t_systemuserinfo a,  
53.                t_genworkflow c,  
54.                t_batch d,  
55.                t_companyinfotmp e,  
56.                (select distinct batchid,  
57.                                 companyid,  
58.                                 userid,  
59.                                 direction,  
60.                                 fromwkfid,  
61.                                 towkfid  
62.                   from dbo.T_WorkflowLog  
63.                  where dotime between '2008-09-02' and '2008-09-03') as b  
64.          where a.systemuserid = b.userid  
65.            and b.fromwkfid = c.genworkflowid  
66.            and b.batchid = d.batchid  
67.            and b.companyid = e.companyid  
68.            and b.batchid = e.batchid  
69.          group by a.username, c.workflowname, d.batchcode) f,  
70.        (select a.username,  
71.                d.batchcode,  
72.                c.workflowname,  
73.                count(*) trueworkload  
74.           from t_systemuserinfo a,  
75.                t_genworkflow c,  
76.                t_batch d,  
77.                t_companyinfotmp e,  
78.                (select distinct batchid,  
79.                                 companyid,  
80.                                 userid,  
81.                                 direction,  
82.                                 fromwkfid,  
83.                                 towkfid  
84.                   from dbo.T_WorkflowLog  
85.                  where dotime between '2008-09-02' and '2008-09-03') as b  
86.          where a.systemuserid = b.userid  
87.            and b.fromwkfid = c.genworkflowid  
88.            and b.batchid = d.batchid  
89.            and b.companyid = e.companyid  
90.            and e.datastatus = 1  
91.            and b.batchid = e.batchid  
92.          group by a.username, c.workflowname, d.batchcode, e.datastatus) g  
93.  where f.batchcode = g.batchcode  
94.    and f.workflowname = g.workflowname  
95.    and f.username = g.username compute sum(workload),  
96.  sum(trueworkload)