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

最新推荐文章于 2025-11-03 08:16:09 发布

原创最新推荐文章于 2025-11-03 08:16:09 发布 · 9k 阅读

6 ·

CC 4.0 BY-SA版权

文章标签：

#sql #c

sql server 专栏收录该内容

12 篇文章

订阅专栏

本文介绍了一个复杂的SQL查询案例，该查询旨在对比不同时间段内用户的实际工作负载与预估工作负载。通过对多个表进行联接操作，并使用子查询来筛选特定日期范围内的数据，最终实现了精确的数据对比。

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)