做泛微OA实施的人十有八九会遇到这种场景流程明明审批完了表单数据也在到了做报表统计的时候却发现总数对不上。查来查去一部分数据还在流程表单主表里另一部分已经进了归档表。两张表结构差不多可你想用一条SQL把这两部分同时查出来就需要一点门道了。我打算从泛微OA流程表单的数据形态讲起把归档表与未归档表的结构差异、定位方法、联表查询SQL、统计场景和排查经验一次说清楚。内容偏实操适合正在做泛微OA实施、二次开发、报表对接的同事拿去就能用。就算你之前没碰过泛微数据库按照文章里的思路也能自己摸出来。1. 泛微OA流程表单的归档机制为什么数据会分成两张表1.1 未归档表单和归档表单在数据存储上有哪些区别未归档的流程表单数据通常存储在formtable_main_xxx这种命名的主表里xxx就是表单建模时生成的ID。只要流程还在审批中或者流程刚结束还没来得及被归档策略处理数据就都在这里。这种表的特征是字段和表单设计器里看到的几乎一一对应但物理字段名往往不友好常见的是field1、field2这种通用命名看不出业务含义。写SQL的时候你得靠表单设计器里的字段顺序、字段标题去反推或者找一份字段映射表否则很容易张冠李戴。归档表则是在系统执行归档任务后把一段时间之前的已结束流程数据从业务主表搬走形成的命名常见的有formtable_arch_xxx、formtable_his_xxx不同版本、不同项目差别很大。系统为什么要做这一步说白了就是为了控制正式表的数据量。你要是跑过几年的泛微就会发现formtable_main_xxx里堆了几十万上百万条数据每次点开单据、提交审批都明显变慢。归档就是把历史包袱甩出去让正式表保持轻盈。归档表的字段结构和主表大体一致但也可能因为版本升级、归档配置、人工调整字段等原因出现差异这也是后面联表查询容易踩坑的地方。1.2 逻辑归档与物理归档先判断你的项目属于哪种很多新手一上来就问我“我的归档表在哪里”其实关键要先搞清楚你们环境里的归档策略是逻辑归档还是物理归档。逻辑归档只是给流程数据打了个状态标记数据本身没有迁移位置表单主表里依然保留了全部记录。这种情况下查询时只要在workflow_requestbase或者对应主表上加一个归档状态过滤条件就行根本不需要跨表。物理归档才是真正把历史数据搬到了单独的归档表里这时候想统计全量数据就必须把两张表合起来查。怎么判断是哪一种方法也很简单先去看后台的“数据归档”功能有没有配置归档周期和归档规则再跑到数据库里查有没有arch、his这类前缀的表然后对比归档前后主表的数据量变化。如果主表数据量一直在涨、没明显减少多半是逻辑归档如果某一天开始主表数据量骤降而归档表里开始有数据那就是物理归档。这一步判断错了后面所有统计都会出问题。我见过有人不知道环境是逻辑归档非要做UNION ALL结果同一个流程数据在主表和归档表里各查了一遍报表数字直接翻倍。2. 动手查之前先把表结构和关键字段摸清楚2.1 泛微OA常用表与字段对应关系写联表SQL之前先对常用表有个底。泛微E-cology里最常用到的几张表大概是这样的表名角色关键字段workflow_requestbase流程请求主表requestid流程请求ID、workflowid流程ID、requestname流程标题、creater创建人ID、createrdepartment创建部门ID、createdate创建日期workflow_base流程定义表workflowid、workflowname流程名称workflow_flownode流程节点表nodeid、nodename节点名称、workflowidworkflow_currentoperator当前操作者表requestid、nodeid、useridformtable_main_xxx未归档表单主表id主表自增ID、requestid、creater、createdate、field1...业务字段formtable_detail_xxx表单明细表id、mainid关联主表id、requestid部分版本有、明细业务字段formtable_arch_xxx归档表单表与主表结构类似hrmresource人员表id、lastnamehrmdepartment部门表id、departmentname这里有个特别容易忽略的点workflow_requestbase里的creater存的是人员ID不是姓名createrdepartment存的是部门ID不是部门名称。你要是直接select creater出来的是一串数字肯定懵。联表查询时老老实实关联hrmresource和hrmdepartment取名称这才是正确姿势。同理流程名称也要通过workflow_base关联出来不要在workflow_requestbase里找。2.2 三步定位你的表单对应的物理表如果后台能看到表单ID那直接按formtable_main_表单ID去找就行。怕就怕客户环境是别人搭的没留文档表单ID也不知道在哪。这时候我一般用三步来定位。第一步到数据库里查所有表单表SQL Server环境可以这样写SELECT name, create_date, modify_date FROM sys.tables WHERE name LIKE formtable[_]main[_]% ORDER BY modify_date DESC;这里的[_]是转义写法因为_在LIKE里是通配符如果不加方括号它会把formtableXmainX这种奇怪的表名也匹配出来。按modify_date倒序排列最近改过的表会靠前配合表单设计器的修改时间基本一眼就能定位。第二步对照表单设计器里的字段。打开流程表单的设计页面看看有哪些字段再到数据库里执行SELECT TOP 50 * FROM formtable_main_xxx把物理字段和数据值比对一遍。field1、field2到底对应申请事由还是金额靠猜是猜不出来的只有数据比对最可靠。第三步注意一个坑表单一旦删除重建表单ID会变旧数据会留在旧表里。所以如果你发现某个ID对应的表里数据量很少而流程历史数据却很多很可能数据在另一个ID的表下面。这时候按create_date倒序看看有没有更早建的表数据往往就在那里。2.3 明细表和主表是怎么关联的有明细行的流程表单要额外注意。主表formtable_main_xxx有两个关键字段一个是id自增主键一个是requestid关联流程请求。明细表formtable_detail_xxx里通常有一个mainid字段指向主表的id而不是直接指向requestid。很多人第一次写明细关联时习惯性用requestid去关结果明细数据全变成孤儿或者明明有明细却查不出来。我用得比较多的安全写法是先看一眼明细表的数据SELECT TOP 20 * FROM formtable_detail_60;确认清楚里面到底存的是mainid还是requestid再决定关联字段。如果确实用mainid那关联SQL就是这么写的SELECT m.requestid, d.* FROM formtable_main_30 m LEFT JOIN formtable_detail_60 d ON m.id d.mainid;这个关联关系搞错后面统计明细金额、明细数量时一定会翻车。3. 归档与未归档联表查询的三种写法3.1 简单粗暴UNION ALL 先合并再联表把归档和未归档数据一起查出来最直接的办法就是先把两张表合成一张临时结果集再去关联流程表。合并用UNION ALL不要用UNION因为归档和未归档本来就是两个互斥集合不存在需要去重的数据UNION会额外做排序去重数据量大时白白浪费性能。假设未归档表是formtable_main_30归档表是formtable_arch_30两个表结构一致最简单的合并且SELECT requestid, id, creater, createdate, field1, field2 FROM formtable_main_30 UNION ALL SELECT requestid, id, creater, createdate, field1, field2 FROM formtable_arch_30;这里有一点必须注意两个SELECT出来的字段个数、字段顺序要完全一致字段类型最好也一致。如果归档表里某个字段被改成了别的类型SQL Server会做隐式转换轻则性能下降重则直接报错。所以写之前先确认两张表的字段结构不要想当然认为一定一样。再进一步如果要关联流程表和人员表就把上面这段作为一个子查询放进去SELECT b.requestid, b.requestname, b.createdate, r.lastname AS creater_name, t.field1, t.field2, t.data_status FROM ( SELECT requestid, creater, createdate, field1, field2, 未归档 AS data_status FROM formtable_main_30 UNION ALL SELECT requestid, creater, createdate, field1, field2, 已归档 AS data_status FROM formtable_arch_30 ) t LEFT JOIN workflow_requestbase b ON t.requestid b.requestid LEFT JOIN hrmresource r ON t.creater r.id WHERE b.workflowid 123;加了data_status这个字段之后每条数据来自归档表还是未归档表一眼就能看出来。后面不管是排查数据差异还是做自定义报表都能省不少事。3.2 带上流程信息和审批人LEFT JOIN 关联流程表统计报表一般不只查表单字段还要带出流程标题、当前节点、审批人这些信息。这里我强烈建议用LEFT JOIN别用INNER JOIN。原因是归档流程归档后workflow_currentoperator里的临时审批数据可能被清理或者置空如果用内连接这部分流程数据会被直接丢掉统计结果肯定偏少。下面这个SQL可以查每个流程当前所在的节点和当前操作人SELECT b.requestid, b.requestname, n.nodename AS current_node, u.lastname AS current_operator FROM workflow_requestbase b LEFT JOIN workflow_currentoperator co ON b.requestid co.requestid LEFT JOIN workflow_flownode n ON co.nodeid n.nodeid LEFT JOIN hrmresource u ON co.userid u.id WHERE b.workflowid 123;这里要提醒一句LEFT JOIN只能取到流程当前状态的操作者取不到历史审批轨迹。如果客户要找的是“某个节点当时是谁审批的”那就得去查workflow_requestlog那张表里记录了每个节点的操作人、操作时间、审批意见。很多报表需求乍一看好像是在查当前操作人实际上要的是审批轨迹这个需求别搞混了。如果需要把归档未归档表单数据和审批链路一起查可以把3.1里的合并结果当成主表再依次LEFT JOIN流程表、节点表、人员表。主表数据量如果很大优先把流程ID、时间范围这些条件放到子查询内部去过滤别等全部表合并完再WHERE否则性能会很感人。3.3 统计汇总场景子查询聚合后再 JOIN再来一个很常见的需求按申请部门统计流程数量、金额总和。这种场景最忌讳直接把表单表和明细表、流程表全扔到一起GROUP BY因为明细表存在一对多关系一关联就容易把金额翻倍。正确处理顺序是先合并归档与未归档再按requestid聚合明细金额最后再和流程表、部门表关联。比如要统计某个流程下各部门的申请单数量和总金额就可以这样写SELECT dep.departmentname, COUNT(b.requestid) AS total_count, ISNULL(SUM(t.total_amount), 0) AS total_amount FROM workflow_requestbase b LEFT JOIN ( SELECT requestid, SUM(amount) AS total_amount FROM ( SELECT requestid, amount FROM formtable_main_30 UNION ALL SELECT requestid, amount FROM formtable_arch_30 ) a GROUP BY requestid ) t ON b.requestid t.requestid LEFT JOIN hrmdepartment dep ON b.createrdepartment dep.id WHERE b.workflowid 123 GROUP BY dep.departmentname;这个写法最大的好处是先把一对多的明细行压成一行后续无论怎么关联都不会翻倍。COUNT用workflow_requestbase里的requestid不会受子查询影响。如果数据库是MySQL把ISNULL换成IFNULL其他逻辑都一样。金额字段如果是NULL聚合结果会变成NULL所以要么用ISNULL包一层要么在查询前先把空值处理掉。4. 实战中常见的四个坑和排查办法4.1 找不到归档表或者归档表是空的我在客户现场不止一次遇到这样的情况文档上说有归档表结果数据库里怎么都找不到。先别急着怀疑文档去看看sys.tables里有没有名字带his、arch、old这类关键字的表。有些版本归档表不是叫formtable_arch_30而是formtable_his_30或者归档任务执行后归档表才会被创建没执行之前根本不存在。还有一种情况是归档表存在但里面一条数据都没有。这个多半是因为归档条件设置得太苛刻比如要求流程结束超过365天才归档数据自然还没进去。这时候不要死磕归档表先到后台的“数据归档”菜单里看看归档策略的执行日志确认上一次归档是否成功。如果归档任务一直跑却始终没有数据进来就得排查是不是归档时筛选条件有误比如把requestmark、workflowid之类的过滤字段写错了。4.2 联表之后数据翻倍、记录重复出现数据翻倍九成都是关联关系写错了。最常见的是主表和明细表一对多主表一条记录JOIN出明细表三条记录统计总数时自然翻倍。还有一种情况是workflow_currentoperator里同一个请求存在多个节点、多个操作人主表一JOIN就一堆重复行。排查办法是把问题拆开看第一步单独查主表总数SELECT COUNT(1) FROM formtable_main_30;第二步按requestid去重查明细表总数SELECT COUNT(DISTINCT requestid) FROM formtable_detail_60;第三步再跑完整联表SQL看结果和第一步是否能对上。如果对不上逐层缩小范围直到定位到是哪一次JOIN把数据撑大了。有人图省事上来就加DISTINCT结果虽然数字看着对了但明细数据被悄悄吃掉一堆反而更危险。正确做法是把一对多的地方先用子查询聚合再参与JOIN。如果只是要保留每个流程的最新一条明细可以用ROW_NUMBER()窗口函数。这个函数在SQL Server 2008 R2里也支持SELECT requestid, field1, field2, ROW_NUMBER() OVER(PARTITION BY requestid ORDER BY id DESC) AS rn FROM formtable_detail_60;外层再用WHERE rn 1就能取到每个流程ID下的最新一条明细记录。注意老版本SQL Server对窗口函数的支持有限像LAG、LEAD要2012之后才有写SQL前先确认对方数据库版本。4.3 数据口径对不上归档前后字段对不齐数据对不齐是另一个高频问题。有些项目是中途升级过泛微版本或者表单改过字段导致归档表里根本没有新加的字段新字段在老数据里全是空值还有的归档表和老主表字段顺序不一致你用SELECT *合并时位置对不上数据就错位了。我碰到过最离谱的一次归档表把某个金额字段从decimal改成了varchar合并查询时一直报转换错误查了半天才发现是类型问题。解决办法是在写联表SQL之前先把两张表的字段结构拉出来对比一下SELECT c.name AS column_name, t.name AS data_type, c.max_length FROM sys.columns c JOIN sys.types t ON c.user_type_id t.user_type_id WHERE c.object_id OBJECT_ID(formtable_main_30) UNION ALL SELECT c.name AS column_name, t.name AS data_type, c.max_length FROM sys.columns c JOIN sys.types t ON c.user_type_id t.user_type_id WHERE c.object_id OBJECT_ID(formtable_arch_30);把两边结果放到Excel里做一次字段比对哪些字段多了、哪些类型变了就一目了然。如果确实存在类型不一致就在合并查询里用CAST或者CONVERT统一类型比如把归档表里的varchar金额统一转成decimalSELECT requestid, CONVERT(decimal(18,2), amount) AS amount FROM formtable_arch_30;4.4 查询越来越慢索引和SQL写法怎么调整数据量上来以后联表查询慢是必然的。我经手过的泛微环境表单主表几百万条数据很常见如果不做优化一个统计SQL跑十几分钟很正常。优先做这几件事第一合并查询前尽量过滤。比如只查近一年的数据那就在两个子查询里分别加上时间条件让每个表都先缩小数据范围再合并。不要合并完几百万行再WHERE差别非常大。第二给关联字段建索引。formtable_main_xxx.requestid、formtable_arch_xxx.requestid、workflow_requestbase.requestid、workflow_currentoperator.requestid这些字段只要频繁参与JOIN建议都建上索引。索引名自己定义就好比如idx_requestid。已经有索引但还是很慢可以用执行计划看一下是不是没走到索引。第三别在关联字段上套函数。WHERE CONVERT(varchar, createdate, 112) 20240101这种写法索引直接废掉全表扫描绝对慢。改成WHERE createdate 2024-01-01 AND createdate 2024-01-02才能命中索引。第四能用UNION ALL就别用UNION。前面说过UNION多一步去重排序数据量一大就很伤。归档和未归档本身就是互斥集合不需要去重。第五如果报表场景固定、数据量又大别每次都跨全表跑实时查询。可以考虑建一张定时刷新的统计中间表每天把归档和未归档数据汇总好报表直接查中间表速度能快几个数量级。5. 一些更省事的落地建议5.1 封装视图把归档和未归档合并逻辑固化下来如果你发现某个表单的归档未归档合并查询经常要用与其每次写一遍重复的UNION ALL不如直接在数据库里建一个视图把合并逻辑固化下来CREATE VIEW v_form_30_all AS SELECT requestid, id, creater, createdate, field1, field2, 未归档 AS data_status FROM formtable_main_30 UNION ALL SELECT requestid, id, creater, createdate, field1, field2, 已归档 AS data_status FROM formtable_arch_30;以后写报表SQL时直接FROM v_form_30_all清爽很多。后端做报表、对接第三方系统时别人也不用关心你的数据到底在不在归档表里。不过要提醒一句视图不是一劳永逸的如果表单字段后期有增删视图要跟着改如果归档策略变了比如不再物理归档这个视图反而会制造重复数据需要及时下线。如果觉得视图字段太固定、不好维护也可以用存储过程把字段列表拼出来。但这带来的问题是维护成本高而且动态SQL容易引入安全隐患。我的原则是能不用动态SQL就不用视图能解决的尽量用视图只有字段确实经常变动、又不想每次改视图的时候才考虑存储过程方案。5.2 数据权限与维护注意事项报表查询通常需要开通数据库账号这里有几个细节值得重视。账号权限尽量给最小化能只读查询就只给SELECT不要顺手给了UPDATE、DELETE权限。生产环境上误操作删错数据的事情我见过不止一次权限收紧一点能挡住一大半事故。另外如果报表平台支持参数化查询尽量不要把前端参数直接拼接进SQL里。一方面是防SQL注入风险另一方面是参数化查询对数据库执行计划更友好相同结构的SQL能走缓存性能也更稳定。这一点在给客户做报表接口时尤其重要别图省事拼字符串。对接金蝶等第三方系统做数据同步时我也建议直接基于业务视图去拉数据。这样业务表结构变化时只改视图不需要动集成接口能少掉很多因为字段对不上导致的同步失败工单。同步时记得加上增量条件比如按requestid或createdate做增量避免每次全量拉取。最后说一点个人体会。我手里维护过好几套泛微环境几乎每次收到“统计数据偏少”的工单最后都指向同一个原因——归档表没查。后来我给自己定了个规矩任何涉及流程表单的统计SQL动手之前先花十分钟查表结构、确认归档策略再决定是加字段过滤还是UNION ALL。做到这一步再写联表查询基本不会翻车。希望这篇内容能帮你少踩几个坑。
做泛微OA实施的人十有八九会遇到这种场景流程明明审批完了表单数据也在到了做报表统计的时候却发现总数对不上。查来查去一部分数据还在流程表单主表里另一部分已经进了归档表。两张表结构差不多可你想用一条SQL把这两部分同时查出来就需要一点门道了。我打算从泛微OA流程表单的数据形态讲起把归档表与未归档表的结构差异、定位方法、联表查询SQL、统计场景和排查经验一次说清楚。内容偏实操适合正在做泛微OA实施、二次开发、报表对接的同事拿去就能用。就算你之前没碰过泛微数据库按照文章里的思路也能自己摸出来。1. 泛微OA流程表单的归档机制为什么数据会分成两张表1.1 未归档表单和归档表单在数据存储上有哪些区别未归档的流程表单数据通常存储在formtable_main_xxx这种命名的主表里xxx就是表单建模时生成的ID。只要流程还在审批中或者流程刚结束还没来得及被归档策略处理数据就都在这里。这种表的特征是字段和表单设计器里看到的几乎一一对应但物理字段名往往不友好常见的是field1、field2这种通用命名看不出业务含义。写SQL的时候你得靠表单设计器里的字段顺序、字段标题去反推或者找一份字段映射表否则很容易张冠李戴。归档表则是在系统执行归档任务后把一段时间之前的已结束流程数据从业务主表搬走形成的命名常见的有formtable_arch_xxx、formtable_his_xxx不同版本、不同项目差别很大。系统为什么要做这一步说白了就是为了控制正式表的数据量。你要是跑过几年的泛微就会发现formtable_main_xxx里堆了几十万上百万条数据每次点开单据、提交审批都明显变慢。归档就是把历史包袱甩出去让正式表保持轻盈。归档表的字段结构和主表大体一致但也可能因为版本升级、归档配置、人工调整字段等原因出现差异这也是后面联表查询容易踩坑的地方。1.2 逻辑归档与物理归档先判断你的项目属于哪种很多新手一上来就问我“我的归档表在哪里”其实关键要先搞清楚你们环境里的归档策略是逻辑归档还是物理归档。逻辑归档只是给流程数据打了个状态标记数据本身没有迁移位置表单主表里依然保留了全部记录。这种情况下查询时只要在workflow_requestbase或者对应主表上加一个归档状态过滤条件就行根本不需要跨表。物理归档才是真正把历史数据搬到了单独的归档表里这时候想统计全量数据就必须把两张表合起来查。怎么判断是哪一种方法也很简单先去看后台的“数据归档”功能有没有配置归档周期和归档规则再跑到数据库里查有没有arch、his这类前缀的表然后对比归档前后主表的数据量变化。如果主表数据量一直在涨、没明显减少多半是逻辑归档如果某一天开始主表数据量骤降而归档表里开始有数据那就是物理归档。这一步判断错了后面所有统计都会出问题。我见过有人不知道环境是逻辑归档非要做UNION ALL结果同一个流程数据在主表和归档表里各查了一遍报表数字直接翻倍。2. 动手查之前先把表结构和关键字段摸清楚2.1 泛微OA常用表与字段对应关系写联表SQL之前先对常用表有个底。泛微E-cology里最常用到的几张表大概是这样的表名角色关键字段workflow_requestbase流程请求主表requestid流程请求ID、workflowid流程ID、requestname流程标题、creater创建人ID、createrdepartment创建部门ID、createdate创建日期workflow_base流程定义表workflowid、workflowname流程名称workflow_flownode流程节点表nodeid、nodename节点名称、workflowidworkflow_currentoperator当前操作者表requestid、nodeid、useridformtable_main_xxx未归档表单主表id主表自增ID、requestid、creater、createdate、field1...业务字段formtable_detail_xxx表单明细表id、mainid关联主表id、requestid部分版本有、明细业务字段formtable_arch_xxx归档表单表与主表结构类似hrmresource人员表id、lastnamehrmdepartment部门表id、departmentname这里有个特别容易忽略的点workflow_requestbase里的creater存的是人员ID不是姓名createrdepartment存的是部门ID不是部门名称。你要是直接select creater出来的是一串数字肯定懵。联表查询时老老实实关联hrmresource和hrmdepartment取名称这才是正确姿势。同理流程名称也要通过workflow_base关联出来不要在workflow_requestbase里找。2.2 三步定位你的表单对应的物理表如果后台能看到表单ID那直接按formtable_main_表单ID去找就行。怕就怕客户环境是别人搭的没留文档表单ID也不知道在哪。这时候我一般用三步来定位。第一步到数据库里查所有表单表SQL Server环境可以这样写SELECT name, create_date, modify_date FROM sys.tables WHERE name LIKE formtable[_]main[_]% ORDER BY modify_date DESC;这里的[_]是转义写法因为_在LIKE里是通配符如果不加方括号它会把formtableXmainX这种奇怪的表名也匹配出来。按modify_date倒序排列最近改过的表会靠前配合表单设计器的修改时间基本一眼就能定位。第二步对照表单设计器里的字段。打开流程表单的设计页面看看有哪些字段再到数据库里执行SELECT TOP 50 * FROM formtable_main_xxx把物理字段和数据值比对一遍。field1、field2到底对应申请事由还是金额靠猜是猜不出来的只有数据比对最可靠。第三步注意一个坑表单一旦删除重建表单ID会变旧数据会留在旧表里。所以如果你发现某个ID对应的表里数据量很少而流程历史数据却很多很可能数据在另一个ID的表下面。这时候按create_date倒序看看有没有更早建的表数据往往就在那里。2.3 明细表和主表是怎么关联的有明细行的流程表单要额外注意。主表formtable_main_xxx有两个关键字段一个是id自增主键一个是requestid关联流程请求。明细表formtable_detail_xxx里通常有一个mainid字段指向主表的id而不是直接指向requestid。很多人第一次写明细关联时习惯性用requestid去关结果明细数据全变成孤儿或者明明有明细却查不出来。我用得比较多的安全写法是先看一眼明细表的数据SELECT TOP 20 * FROM formtable_detail_60;确认清楚里面到底存的是mainid还是requestid再决定关联字段。如果确实用mainid那关联SQL就是这么写的SELECT m.requestid, d.* FROM formtable_main_30 m LEFT JOIN formtable_detail_60 d ON m.id d.mainid;这个关联关系搞错后面统计明细金额、明细数量时一定会翻车。3. 归档与未归档联表查询的三种写法3.1 简单粗暴UNION ALL 先合并再联表把归档和未归档数据一起查出来最直接的办法就是先把两张表合成一张临时结果集再去关联流程表。合并用UNION ALL不要用UNION因为归档和未归档本来就是两个互斥集合不存在需要去重的数据UNION会额外做排序去重数据量大时白白浪费性能。假设未归档表是formtable_main_30归档表是formtable_arch_30两个表结构一致最简单的合并且SELECT requestid, id, creater, createdate, field1, field2 FROM formtable_main_30 UNION ALL SELECT requestid, id, creater, createdate, field1, field2 FROM formtable_arch_30;这里有一点必须注意两个SELECT出来的字段个数、字段顺序要完全一致字段类型最好也一致。如果归档表里某个字段被改成了别的类型SQL Server会做隐式转换轻则性能下降重则直接报错。所以写之前先确认两张表的字段结构不要想当然认为一定一样。再进一步如果要关联流程表和人员表就把上面这段作为一个子查询放进去SELECT b.requestid, b.requestname, b.createdate, r.lastname AS creater_name, t.field1, t.field2, t.data_status FROM ( SELECT requestid, creater, createdate, field1, field2, 未归档 AS data_status FROM formtable_main_30 UNION ALL SELECT requestid, creater, createdate, field1, field2, 已归档 AS data_status FROM formtable_arch_30 ) t LEFT JOIN workflow_requestbase b ON t.requestid b.requestid LEFT JOIN hrmresource r ON t.creater r.id WHERE b.workflowid 123;加了data_status这个字段之后每条数据来自归档表还是未归档表一眼就能看出来。后面不管是排查数据差异还是做自定义报表都能省不少事。3.2 带上流程信息和审批人LEFT JOIN 关联流程表统计报表一般不只查表单字段还要带出流程标题、当前节点、审批人这些信息。这里我强烈建议用LEFT JOIN别用INNER JOIN。原因是归档流程归档后workflow_currentoperator里的临时审批数据可能被清理或者置空如果用内连接这部分流程数据会被直接丢掉统计结果肯定偏少。下面这个SQL可以查每个流程当前所在的节点和当前操作人SELECT b.requestid, b.requestname, n.nodename AS current_node, u.lastname AS current_operator FROM workflow_requestbase b LEFT JOIN workflow_currentoperator co ON b.requestid co.requestid LEFT JOIN workflow_flownode n ON co.nodeid n.nodeid LEFT JOIN hrmresource u ON co.userid u.id WHERE b.workflowid 123;这里要提醒一句LEFT JOIN只能取到流程当前状态的操作者取不到历史审批轨迹。如果客户要找的是“某个节点当时是谁审批的”那就得去查workflow_requestlog那张表里记录了每个节点的操作人、操作时间、审批意见。很多报表需求乍一看好像是在查当前操作人实际上要的是审批轨迹这个需求别搞混了。如果需要把归档未归档表单数据和审批链路一起查可以把3.1里的合并结果当成主表再依次LEFT JOIN流程表、节点表、人员表。主表数据量如果很大优先把流程ID、时间范围这些条件放到子查询内部去过滤别等全部表合并完再WHERE否则性能会很感人。3.3 统计汇总场景子查询聚合后再 JOIN再来一个很常见的需求按申请部门统计流程数量、金额总和。这种场景最忌讳直接把表单表和明细表、流程表全扔到一起GROUP BY因为明细表存在一对多关系一关联就容易把金额翻倍。正确处理顺序是先合并归档与未归档再按requestid聚合明细金额最后再和流程表、部门表关联。比如要统计某个流程下各部门的申请单数量和总金额就可以这样写SELECT dep.departmentname, COUNT(b.requestid) AS total_count, ISNULL(SUM(t.total_amount), 0) AS total_amount FROM workflow_requestbase b LEFT JOIN ( SELECT requestid, SUM(amount) AS total_amount FROM ( SELECT requestid, amount FROM formtable_main_30 UNION ALL SELECT requestid, amount FROM formtable_arch_30 ) a GROUP BY requestid ) t ON b.requestid t.requestid LEFT JOIN hrmdepartment dep ON b.createrdepartment dep.id WHERE b.workflowid 123 GROUP BY dep.departmentname;这个写法最大的好处是先把一对多的明细行压成一行后续无论怎么关联都不会翻倍。COUNT用workflow_requestbase里的requestid不会受子查询影响。如果数据库是MySQL把ISNULL换成IFNULL其他逻辑都一样。金额字段如果是NULL聚合结果会变成NULL所以要么用ISNULL包一层要么在查询前先把空值处理掉。4. 实战中常见的四个坑和排查办法4.1 找不到归档表或者归档表是空的我在客户现场不止一次遇到这样的情况文档上说有归档表结果数据库里怎么都找不到。先别急着怀疑文档去看看sys.tables里有没有名字带his、arch、old这类关键字的表。有些版本归档表不是叫formtable_arch_30而是formtable_his_30或者归档任务执行后归档表才会被创建没执行之前根本不存在。还有一种情况是归档表存在但里面一条数据都没有。这个多半是因为归档条件设置得太苛刻比如要求流程结束超过365天才归档数据自然还没进去。这时候不要死磕归档表先到后台的“数据归档”菜单里看看归档策略的执行日志确认上一次归档是否成功。如果归档任务一直跑却始终没有数据进来就得排查是不是归档时筛选条件有误比如把requestmark、workflowid之类的过滤字段写错了。4.2 联表之后数据翻倍、记录重复出现数据翻倍九成都是关联关系写错了。最常见的是主表和明细表一对多主表一条记录JOIN出明细表三条记录统计总数时自然翻倍。还有一种情况是workflow_currentoperator里同一个请求存在多个节点、多个操作人主表一JOIN就一堆重复行。排查办法是把问题拆开看第一步单独查主表总数SELECT COUNT(1) FROM formtable_main_30;第二步按requestid去重查明细表总数SELECT COUNT(DISTINCT requestid) FROM formtable_detail_60;第三步再跑完整联表SQL看结果和第一步是否能对上。如果对不上逐层缩小范围直到定位到是哪一次JOIN把数据撑大了。有人图省事上来就加DISTINCT结果虽然数字看着对了但明细数据被悄悄吃掉一堆反而更危险。正确做法是把一对多的地方先用子查询聚合再参与JOIN。如果只是要保留每个流程的最新一条明细可以用ROW_NUMBER()窗口函数。这个函数在SQL Server 2008 R2里也支持SELECT requestid, field1, field2, ROW_NUMBER() OVER(PARTITION BY requestid ORDER BY id DESC) AS rn FROM formtable_detail_60;外层再用WHERE rn 1就能取到每个流程ID下的最新一条明细记录。注意老版本SQL Server对窗口函数的支持有限像LAG、LEAD要2012之后才有写SQL前先确认对方数据库版本。4.3 数据口径对不上归档前后字段对不齐数据对不齐是另一个高频问题。有些项目是中途升级过泛微版本或者表单改过字段导致归档表里根本没有新加的字段新字段在老数据里全是空值还有的归档表和老主表字段顺序不一致你用SELECT *合并时位置对不上数据就错位了。我碰到过最离谱的一次归档表把某个金额字段从decimal改成了varchar合并查询时一直报转换错误查了半天才发现是类型问题。解决办法是在写联表SQL之前先把两张表的字段结构拉出来对比一下SELECT c.name AS column_name, t.name AS data_type, c.max_length FROM sys.columns c JOIN sys.types t ON c.user_type_id t.user_type_id WHERE c.object_id OBJECT_ID(formtable_main_30) UNION ALL SELECT c.name AS column_name, t.name AS data_type, c.max_length FROM sys.columns c JOIN sys.types t ON c.user_type_id t.user_type_id WHERE c.object_id OBJECT_ID(formtable_arch_30);把两边结果放到Excel里做一次字段比对哪些字段多了、哪些类型变了就一目了然。如果确实存在类型不一致就在合并查询里用CAST或者CONVERT统一类型比如把归档表里的varchar金额统一转成decimalSELECT requestid, CONVERT(decimal(18,2), amount) AS amount FROM formtable_arch_30;4.4 查询越来越慢索引和SQL写法怎么调整数据量上来以后联表查询慢是必然的。我经手过的泛微环境表单主表几百万条数据很常见如果不做优化一个统计SQL跑十几分钟很正常。优先做这几件事第一合并查询前尽量过滤。比如只查近一年的数据那就在两个子查询里分别加上时间条件让每个表都先缩小数据范围再合并。不要合并完几百万行再WHERE差别非常大。第二给关联字段建索引。formtable_main_xxx.requestid、formtable_arch_xxx.requestid、workflow_requestbase.requestid、workflow_currentoperator.requestid这些字段只要频繁参与JOIN建议都建上索引。索引名自己定义就好比如idx_requestid。已经有索引但还是很慢可以用执行计划看一下是不是没走到索引。第三别在关联字段上套函数。WHERE CONVERT(varchar, createdate, 112) 20240101这种写法索引直接废掉全表扫描绝对慢。改成WHERE createdate 2024-01-01 AND createdate 2024-01-02才能命中索引。第四能用UNION ALL就别用UNION。前面说过UNION多一步去重排序数据量一大就很伤。归档和未归档本身就是互斥集合不需要去重。第五如果报表场景固定、数据量又大别每次都跨全表跑实时查询。可以考虑建一张定时刷新的统计中间表每天把归档和未归档数据汇总好报表直接查中间表速度能快几个数量级。5. 一些更省事的落地建议5.1 封装视图把归档和未归档合并逻辑固化下来如果你发现某个表单的归档未归档合并查询经常要用与其每次写一遍重复的UNION ALL不如直接在数据库里建一个视图把合并逻辑固化下来CREATE VIEW v_form_30_all AS SELECT requestid, id, creater, createdate, field1, field2, 未归档 AS data_status FROM formtable_main_30 UNION ALL SELECT requestid, id, creater, createdate, field1, field2, 已归档 AS data_status FROM formtable_arch_30;以后写报表SQL时直接FROM v_form_30_all清爽很多。后端做报表、对接第三方系统时别人也不用关心你的数据到底在不在归档表里。不过要提醒一句视图不是一劳永逸的如果表单字段后期有增删视图要跟着改如果归档策略变了比如不再物理归档这个视图反而会制造重复数据需要及时下线。如果觉得视图字段太固定、不好维护也可以用存储过程把字段列表拼出来。但这带来的问题是维护成本高而且动态SQL容易引入安全隐患。我的原则是能不用动态SQL就不用视图能解决的尽量用视图只有字段确实经常变动、又不想每次改视图的时候才考虑存储过程方案。5.2 数据权限与维护注意事项报表查询通常需要开通数据库账号这里有几个细节值得重视。账号权限尽量给最小化能只读查询就只给SELECT不要顺手给了UPDATE、DELETE权限。生产环境上误操作删错数据的事情我见过不止一次权限收紧一点能挡住一大半事故。另外如果报表平台支持参数化查询尽量不要把前端参数直接拼接进SQL里。一方面是防SQL注入风险另一方面是参数化查询对数据库执行计划更友好相同结构的SQL能走缓存性能也更稳定。这一点在给客户做报表接口时尤其重要别图省事拼字符串。对接金蝶等第三方系统做数据同步时我也建议直接基于业务视图去拉数据。这样业务表结构变化时只改视图不需要动集成接口能少掉很多因为字段对不上导致的同步失败工单。同步时记得加上增量条件比如按requestid或createdate做增量避免每次全量拉取。最后说一点个人体会。我手里维护过好几套泛微环境几乎每次收到“统计数据偏少”的工单最后都指向同一个原因——归档表没查。后来我给自己定了个规矩任何涉及流程表单的统计SQL动手之前先花十分钟查表结构、确认归档策略再决定是加字段过滤还是UNION ALL。做到这一步再写联表查询基本不会翻车。希望这篇内容能帮你少踩几个坑。
