sqlserver兩種查詢方式效率比較
方式1
SELECT a.EmpNo,a.AttDate,CASE WHEN (a.TStTime>=a.DinStTime and a.TStTime<=a.DinEnTime) THEN a.DinEnTime ELSE a.TStTime END AS RStTime,CASE WHEN (a.TEnTime>=a.DinStTime and a.TEnTime <= a.DinEnTime) THEN a.DinStTime ELSE a.TEnTime END AS REnTime,(SELECT count(b.EmpNo) from leave_view_temp2 b WHERE a.EmpNo=b.EmpNo AND a.AttDate=b.AttDate group by b.EmpNo,b.AttDate) AS LTimes FROM leave_view_temp2 a
(67960 row(s) affected)耗時1min53s
方式2
SELECT a.EmpNo,a.AttDate,CASE WHEN (a.TStTime>=a.DinStTime and a.TStTime<=a.DinEnTime) THEN a.DinEnTime ELSE a.TStTime END AS RStTime,CASE WHEN (a.TEnTime>=a.DinStTime and a.TEnTime <= a.DinEnTime) THEN a.DinStTime ELSE a.TEnTime END AS REnTime,b.LTimes FROM leave_view_temp2 a INNER JOIN (SELECT EmpNo,AttDate,count(EmpNo) as LTimes from leave_view_temp2 group by EmpNo,AttDate) b ON a.EmpNo=b.EmpNo AND a.AttDate=b.AttDate
(67960 row(s) affected)耗時9s
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步