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