当前位置： 首页 > news >正文

CRM系统签到列表数据重复BUG排查与修复（SQL Server存储过程）

news 2026/5/31 0:59:50

CRM系统签到列表数据重复BUG
排查与修复（SQL Server存储过程）

问题现象

任我行协同CRM系统中，工作轨迹模块的签到列表出现数据重复：

  • 同一个客户出现2 次
  • 同一签到记录显示2 条
  • 关联的"外勤签到转日程"也重复

以用户"XXX"为例，2026-05-21 的签到数据全部翻倍显示。

排查过程

第一步：定位查询来源

签到列表的数据来自存储过程spCRM_SelectSignInList，该过程使用UNION ALL合并两个子查询：

签到子查询 (CRM_SignIn, ObjectType=77) UNION ALL 日程子查询 (CRM_Schedule, ObjectType=15)

第二步：发现 BUG1 — 自动生成日程混入

签到时如果IsRollSchedule=True，系统会在CRM_Schedule表自动创建一条"外勤签到转日程-外勤签到"记录，标记CoObjectType='SignIn'。

结果：同一个行为在列表中出现了两次——第1次来自签到子查询（ObjType=77），第2次来自日程子查询（ObjType=15）。数据库中此类自动生成日程共5,645 条。

修复 v1：在日程子查询 WHERE 条件中排除签到自动生成的日程：

AND (a.CoObjectType IS NULL OR a.CoObjectType <> 'SignIn')
CoObjectType IS NULL 兼容普通日程（非签到转来的），<> 'SignIn' 排除签到自动生成的日程。

第三步：v1 部署后仍重复！

用户反馈修复后仍然重复。进一步分析发现：重复的全部是 ObjectType=77（签到本身），不是日程。说明签到子查询内部还有问题。

第四步：发现 BUG2 — 头像 LEFT JOIN 笛卡尔积

签到子查询用LEFT JOIN CRM_UploadFile取员工头像：

-- 原代码（有BUG） LEFT JOIN CRM_UploadFile d ON d.CoObjectTypeID = a.Creator AND d.CoObject = 'HeadPicture'

查询数据库发现：XXX（Creator=0000000004）在 CRM_UploadFile 表中有 2 个头像记录（ID=32351 和 32352，均于2020年上传）。LEFT JOIN 时每条签到匹配到 2 个头像 → 输出 2 行。

签到 ID描述匹配头像数输出行数
6365测试22 ❌
6366嘉澜22 ❌
6363(ZXX)11 ✅

修复 v2：用OUTER APPLY (SELECT TOP 1 ...)替代LEFT JOIN，只取最新头像：

-- 修复后 OUTER APPLY ( SELECT TOP 1 FilePath FROM CRM_UploadFile WHERE CoObjectTypeID = a.Creator AND CoObject = 'HeadPicture' ORDER BY ID DESC ) d

ORDER BY ID DESC取最新上传的头像，TOP 1保证只返回一行，杜绝笛卡尔积。

根因总结

这是两层笛卡尔积叠加导致的重复：

层级根因影响机制
BUG1（外层）UNION ALL 把自动生成日程也查出来签到 + 日程 = 每条行为出现 2 次
BUG2（内层）头像 LEFT JOIN 一人多头像胡玉发 2 个头像 → 每个签到 ×2

极端情况：IsRollSchedule=True + 有 2 个头像 → 单条签到显示4 次。

完整修复脚本

-- ============================================= -- 修复：工作轨迹签到列表重复BUG（完整版 v2） -- 日期：2026-05-21 -- BUG1：UNION ALL 导致签到自动生成的日程重复 -- BUG2：LEFT JOIN CRM_UploadFile 头像笛卡尔积 -- ============================================= USE [grasp_crm04] GO ALTER PROCEDURE [dbo].[spCRM_SelectSignInList] ( @CoScheduleTypeId VARCHAR(30), @Creator VARCHAR(30), @CurrentUser VARCHAR(30), @Keywords NVARCHAR(50), @Department VARCHAR(30), @UserGroup INT, @BeginDate DATETIME, @EndDate DATETIME, @SignInRange INT, @Flag INT = 0, @pageIndex INT, @pageSize INT, @WorkTraceSignOut INT, @TotalCount INT OUTPUT ) AS BEGIN DECLARE @sql NVARCHAR(max), @filter NVARCHAR(max), @child NVARCHAR(max) SET @child = '' SET @TotalCount = 0 DECLARE @startRow INT, @endRow INT SET @startRow = (@pageIndex - 1) * @pageSize + 1 SET @endRow = @startRow + @pageSize - 1 -- 权限控制 DECLARE @bitEmployeeIsAdmin BIT, @bitEmployeeIsManager BIT DECLARE @bitViewPersonalLimit BIT, @bitViewDepartmentLimit BIT DECLARE @isNone BIT, @chvEmployeeDepartment VARCHAR(30) SELECT @bitEmployeeIsAdmin = isAdmin, @bitEmployeeIsManager = isAll, @bitViewPersonalLimit = isSelf, @bitViewDepartmentLimit = isDep, @isNone = isNone, @chvEmployeeDepartment = Department FROM fn_getUserViewRightRange(@CurrentUser, 'WorkTraceView') IF @isNone = 1 BEGIN SELECT * FROM CRM_SignIn WHERE [ID] = 0 RETURN END DECLARE @sortfilter VARCHAR(100) IF @Flag = 0 SET @sortfilter = 'a.CreateDate desc' ELSE SET @sortfilter = 'a.CreateDate asc' DECLARE @employeewhere VARCHAR(2000) SET @employeewhere = ' 1=1 ' IF @bitEmployeeIsAdmin <> 1 AND @bitEmployeeIsManager <> 1 BEGIN IF @bitViewDepartmentLimit = 1 SET @employeewhere = @employeewhere + ' and (ISNULL(b.[Department],''001'') IN (SELECT Department FROM dbo.CRM_WorkTraceVisibleDepartments WHERE [User]=''' + @CurrentUser + ''') Or b.[TypeID] =''' + @CurrentUser + ''')' ELSE IF @bitViewPersonalLimit = 1 SET @employeewhere = @employeewhere + ' and b.[TypeID] =''' + @CurrentUser + '''' ELSE SET @employeewhere = @employeewhere + ' and 1=2 ' END SET @filter = ' where 1=1 ' IF ISNULL(@CoScheduleTypeId, '') <> '' SET @filter = @filter + ' and CoScheduleTypeId =''' + @CoScheduleTypeId + ''' ' IF ISNULL(@Keywords, '') <> '' SET @filter = @filter + ' and (a.Description like @Keywords or a.Address like @Keywords) ' IF ISNULL(@Creator, '') <> '' SET @filter = @filter + ' and a.Creator=@Creator ' ELSE SET @employeewhere = @employeewhere + ' and b.IsActive=1 ' IF ISNULL(@BeginDate, '') <> '' SET @filter = @filter + ' and a.CreateDate>=@BeginDate ' IF ISNULL(@EndDate, '') <> '' SET @filter = @filter + ' and a.CreateDate<=@EndDate ' DECLARE @tempsql VARCHAR(200) SET @tempsql = '' IF @WorkTraceSignOut = 0 SET @tempsql = ' and a.id not in(SELECT distinct CoSignInID FROM dbo.CRM_SignOut) ' IF @WorkTraceSignOut = 1 SET @tempsql = ' and a.id in(SELECT distinct CoSignInID FROM dbo.CRM_SignOut) ' IF ISNULL(@UserGroup, 0) <> 0 BEGIN DECLARE @groupUser VARCHAR(max) SELECT @groupUser = ReceiverTypeID FROM CRM_MsgReceiverGroup WHERE id = @UserGroup SET @filter = @filter + ' and a.Creator in(select a from dbo.f_split(''' + @groupUser + ''','','')) ' END IF ISNULL(@Department, '') <> '' SET @filter = @filter + ' and a.Creator in(SELECT TypeID FROM dbo.CRM_Employee WHERE Department like @Department+''%'' OR Department2 like @Department+''%'' OR Department3 like @Department+''%'') ' -- ============ 签到子查询 ============ IF @SignInRange & 2 = 2 SET @child = @child + ' select a.ID, a.Creator, a.CreateDate, a.Description, a.ReplyAmount, a.SupportAmount, a.Longitude, a.Latitude, a.Address, b.Name as CreatorName, c.ID as SupportID, d.FilePath AS HeadPhoto, cast(a.ID as varchar(30)) TypeID, ObjectType=77, b.Department, Style=1, a.IsRollSchedule, a.IsRollAttendance, a.ModifyDate, a.CheckStatus, a.ScheduleStyle, a.CoScheduleTypeId from CRM_SignIn a inner join CRM_Employee b on b.TypeID=a.Creator -- [修复 BUG2] OUTER APPLY 避免多头像笛卡尔积 OUTER APPLY ( SELECT TOP 1 FilePath FROM CRM_UploadFile WHERE CoObjectTypeID=a.Creator AND CoObject=''HeadPicture'' ORDER BY ID DESC ) d LEFT JOIN dbo.CRM_CoEvaluate c ON c.CoObjectTypeID=a.ID and c.CreateTypeID=@CurrentUser and c.CoObject=''SignIn'' where ' + @employeewhere + @tempsql IF @SignInRange & 3 = 3 SET @child = @child + ' union all ' -- ============ 日程子查询 ============ IF @SignInRange & 1 = 1 SET @child = @child + ' SELECT a.ID, a.CreatorTypeID Creator, a.CreateDate, a.[Subject] Description, f.ReplyAmount, e.SupportAmount, a.Longitude, a.Latitude, a.AdressName Address, b.Name CreatorName, c.ID as SupportID, d.FilePath AS HeadPhoto, a.TypeID, ObjectType=15, a.Department, a.Style, 0 as IsRollSchedule, 0 as IsRollAttendance, a.ModifyDate, '''' as CheckStatus, 0 as ScheduleStyle, '''' as CoScheduleTypeId FROM dbo.CRM_Schedule a INNER JOIN dbo.CRM_Employee b ON b.TypeID=a.CreatorTypeID -- [修复 BUG2] OUTER APPLY 避免多头像笛卡尔积 OUTER APPLY ( SELECT TOP 1 FilePath FROM CRM_UploadFile WHERE CoObjectTypeID=a.CreatorTypeID AND CoObject=''HeadPicture'' ORDER BY ID DESC ) d LEFT JOIN dbo.CRM_CoEvaluate c ON c.CoObjectTypeID=a.TypeID and c.CreateTypeID=@CurrentUser and c.CoObject=''Schedule'' LEFT JOIN ( SELECT COUNT(0) SupportAmount, CoObjectTypeID FROM CRM_CoEvaluate WHERE CoObject=''Schedule'' and Evaluate=''Praise'' GROUP BY CoObjectTypeID ) e ON e.CoObjectTypeID=a.TypeID left join ( SELECT COUNT(0) ReplyAmount, CoScheduleTypeID FROM dbo.CRM_SchedulePostil GROUP BY CoScheduleTypeID ) f on f.CoScheduleTypeID=a.TypeID WHERE ISNULL(a.Longitude,0)<>0 AND ISNULL(a.Latitude,0)<>0 -- [修复 BUG1] 排除签到自动生成的日程 AND (a.CoObjectType IS NULL OR a.CoObjectType <> ''SignIn'') and ' + @employeewhere -- 总数查询 SET @sql = 'select @TotalCount=count(0) from ( ' + @child + ' ) as a ' + @filter EXECUTE sp_executesql @sql, N'@Creator varchar(30),@CurrentUser varchar(30), @chvEmployeeDepartment varchar(30), @Keywords NVARCHAR(50),@BeginDate datetime, @EndDate datetime,@Department varchar(30), @TotalCount INT OUTPUT', @Creator, @CurrentUser, @chvEmployeeDepartment, @Keywords, @BeginDate, @EndDate, @Department, @TotalCount OUTPUT IF @pageIndex = -1 BEGIN SET @sql = ' select * from ( select ROW_NUMBER() OVER( ORDER BY a.Creator asc,' + @sortfilter + ' ) AS rowNum, ID, Creator, CreateDate, Description, ReplyAmount, SupportAmount, Longitude, Latitude, Address, CreatorName, SupportID, HeadPhoto, TypeID, ObjectType, Department, Style, IsRollSchedule, IsRollAttendance, ModifyDate, CheckStatus, ScheduleStyle, CoScheduleTypeId from ( ' + @child + ' ) as a ' + @filter + ' ) as temp ' EXECUTE sp_executesql @sql, N'@Creator varchar(30),@CurrentUser varchar(30), @chvEmployeeDepartment varchar(30), @Keywords NVARCHAR(50),@BeginDate datetime, @EndDate datetime,@Department varchar(30), @startRow int,@endRow int', @Creator, @CurrentUser, @chvEmployeeDepartment, @Keywords, @BeginDate, @EndDate, @Department, @startRow, @endRow END ELSE BEGIN SET @sql = ' select * from ( select ROW_NUMBER() OVER( ORDER BY a.Creator asc,' + @sortfilter + ' ) AS rowNum, ID, Creator, CreateDate, Description, ReplyAmount, SupportAmount, Longitude, Latitude, Address, CreatorName, SupportID, HeadPhoto, TypeID, ObjectType, Department, Style, IsRollSchedule, IsRollAttendance, ModifyDate, CheckStatus, ScheduleStyle, CoScheduleTypeId from ( ' + @child + ' ) as a ' + @filter + ' ) as temp where temp.rowNum >= @startRow and temp.rowNum <= @endRow' EXECUTE sp_executesql @sql, N'@Creator varchar(30),@CurrentUser varchar(30), @chvEmployeeDepartment varchar(30), @Keywords NVARCHAR(50),@BeginDate datetime, @EndDate datetime,@Department varchar(30), @startRow int,@endRow int', @Creator, @CurrentUser, @chvEmployeeDepartment, @Keywords, @BeginDate, @EndDate, @Department, @startRow, @endRow END END GO

修复效果对比

指标修复前修复后
胡玉发签到数4条（2签到 × 2头像）2条✅
ObjType 分布混有 77 和 15全部 77✅
日程重复有无✅

经验总结

排查 SQL 数据重复问题时，建议按以下顺序检查：

  1. UNION / UNION ALL是否可能产生重复行
  2. LEFT JOIN 关联字段是否唯一——头像表、附件表、评价表最常见多对一
  3. 源表本身是否有重复数据
  4. 多对多中间表（如协同人表）是否被 JOIN 进来导致膨胀

本次排查关键教训：BUG1（UNION ALL）容易发现，BUG2（头像笛卡尔积）比较隐蔽——因为只有头像数量 > 1 的员工才受影响，其他员工看起来一切正常。排查时要注意缩小范围：先看 ObjType 分布判断是哪层出问题，再从 JOIN 链条逐一检查关联唯一性。

http://www.cnnetsun.cn/news/2530009.html

相关文章：

  • 摆脱论文困扰!2026年最火AI论文写作工具榜单，毕业论文免费写还合规
  • 法律大模型幻觉致败诉案例激增47%？资深刑辩律师手把手教你构建3重事实校验Agent
  • 专业级.NET条码识别与生成：ZXing.Net全面指南
  • 滴滴多篇论文入选 ICML2026，值得一读！
  • FastGithub终极指南：如何5分钟解决GitHub访问缓慢问题
  • 射频线/PCB微带线隔离机理与高衰减器屏蔽设计
  • 在Python中快速接入Taotoken实现多模型调用，告别单一模型依赖
  • 终极指南：如何在5分钟内快速部署Open WebUI开源AI平台
  • 利用Taotoken模型广场为你的智能客服场景选择最合适的大模型
  • 初创团队如何利用Taotoken统一API与多模型能力加速产品原型开发
  • DOM 性能与渲染
  • UE5库存系统设计：FStruct+GameplayTags数据驱动方案
  • 零基础30天掌握渗透测试实战路径
  • kswapd0异常飙升？Linux内核级挖矿攻击深度排查与清除
  • 【MySQL全面教学】MySQL基础SQL语句Day3（2026年）
  • Hurley开源工具：C#到C语言的语义级跨平台翻译
  • JustTrustMe与Frida协同构建Android可信动态分析基座
  • 大模型MoE架构揭秘：为何仅2%参数决定推理性能
  • 企业团队如何利用Taotoken统一管理多项目API密钥与用量
  • DownKyi终极指南：5个技巧让你成为B站视频下载专家
  • Unity Shader从GPU原理入门：顶点与片元着色器硬核解析
  • 观察在流量高峰时段通过Taotoken调用不同模型的响应时间表现
  • Win11Debloat：三步让你的Windows 11告别卡顿，重获新生
  • 【YOLO目标检测全栈实战】69 内存碎片化：量化模型在边缘设备上的隐形杀手
  • Unity手搓合并网格工具：从Draw Call优化到生产级鲁棒性
  • 企业级定制化条形码解析：突破ZXing框架限制的高性能解决方案
  • 3步搞定Spotify音乐永久保存：开源下载神器完全指南
  • CTF自动化实战指南：Web与逆向脚本设计+e春秋靶场API深度利用
  • Unity 2D基础：2D相机Orthographic的参数调节
  • Source Han Serif CN：终极免费字体解决方案快速上手指南