转:http://blog.csdn.net/only_endure/article/details/4020708
版权声明:本文为博主原创文章,未经博主允许不得转载。
- select f.*, g.trueworkload
- from (select a.username, d.batchcode, c.workflowname, count(*) workload
- from t_systemuserinfo a,
- t_genworkflow c,
- t_batch d,
- t_companyinfo e,
- (select distinct batchid,
- companyid,
- userid,
- direction,
- fromwkfid,
- towkfid
- from dbo.T_WorkflowLog
- where dotime between '2008-09-02' and '2008-09-03') as b
- where a.systemuserid = b.userid
- and b.fromwkfid = c.genworkflowid
- and b.batchid = d.batchid
- and b.companyid = e.companyid
- group by a.username, c.workflowname, d.batchcode) f,
- (select a.username,
- d.batchcode,
- c.workflowname,
- count(*) trueworkload
- from t_systemuserinfo a,
- t_genworkflow c,
- t_batch d,
- t_companyinfo e,
- (select distinct batchid,
- companyid,
- userid,
- direction,
- fromwkfid,
- towkfid
- from dbo.T_WorkflowLog
- where dotime between '2008-09-02' and '2008-09-03') as b
- where a.systemuserid = b.userid
- and b.fromwkfid = c.genworkflowid
- and b.batchid = d.batchid
- and b.companyid = e.companyid
- and e.datastatus = 1
- and e.companyid not in
- (select h.companyid
- from t_companyinfotmp h
- where h.batchid = b.batchid)
- group by a.username, c.workflowname, d.batchcode, e.datastatus) g
- where f.batchcode = g.batchcode
- and f.workflowname = g.workflowname
- and f.username = g.username
- union
- select f.*, g.trueworkload
- from (select a.username, d.batchcode, c.workflowname, count(*) workload
- from t_systemuserinfo a,
- t_genworkflow c,
- t_batch d,
- t_companyinfotmp e,
- (select distinct batchid,
- companyid,
- userid,
- direction,
- fromwkfid,
- towkfid
- from dbo.T_WorkflowLog
- where dotime between '2008-09-02' and '2008-09-03') as b
- where a.systemuserid = b.userid
- and b.fromwkfid = c.genworkflowid
- and b.batchid = d.batchid
- and b.companyid = e.companyid
- and b.batchid = e.batchid
- group by a.username, c.workflowname, d.batchcode) f,
- (select a.username,
- d.batchcode,
- c.workflowname,
- count(*) trueworkload
- from t_systemuserinfo a,
- t_genworkflow c,
- t_batch d,
- t_companyinfotmp e,
- (select distinct batchid,
- companyid,
- userid,
- direction,
- fromwkfid,
- towkfid
- from dbo.T_WorkflowLog
- where dotime between '2008-09-02' and '2008-09-03') as b
- where a.systemuserid = b.userid
- and b.fromwkfid = c.genworkflowid
- and b.batchid = d.batchid
- and b.companyid = e.companyid
- and e.datastatus = 1
- and b.batchid = e.batchid
- group by a.username, c.workflowname, d.batchcode, e.datastatus) g
- where f.batchcode = g.batchcode
- and f.workflowname = g.workflowname
- and f.username = g.username compute sum(workload),
- sum(trueworkload)
-
顶
- 0
-
踩