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

本文提供了一段复杂的SQL查询代码,用于统计特定日期范围内不同用户和工作流程的任务量及实际工作量。通过联接多个表并使用子查询,该查询能够细致地分析每个用户的任务完成情况。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

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)  
转载URL:http://blog.youkuaiyun.com/only_endure/article/details/4020708
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值