如何提高这个sql查询

Question

在这里，我以小时和分钟获得了“ProcessTime”，但在几秒钟情况下，只有我检查了相反的情况（见下面的代码）

有什么办法改善吗？

select convert(varchar(10),ScanDate,101) as [Date], tmb.WO, tc.WOCategory, count(IdSingle) QtyProd, tb.Brand, tm.Model, 
     (case when (DateDiff(mi, Min(convert(varchar(10),ScanDate,8)), Max(convert(varchar(10),ScanDate,8)))/60%24 > 0) then 
      convert(varchar(5), DateDiff(mi, Min(convert(varchar(10),ScanDate,8)), Max(convert(varchar(10),ScanDate,8)))/60%24) + 'h '    
       else 
      '' 
     end) + 
     (case when (DateDiff(mi, Min(convert(varchar(10),ScanDate,8)), Max(convert(varchar(10),ScanDate,8)))%60 > 0) then 
      convert(varchar(5), DateDiff(mi, Min(convert(varchar(10),ScanDate,8)), Max(convert(varchar(10),ScanDate,8)))%60) + 'm'    
       else 
      '' 
     end) +   
     (case when 
      (DateDiff(mi, Min(convert(varchar(10),ScanDate,8)), Max(convert(varchar(10),ScanDate,8)))/60%24 <= 0) and 
      (DateDiff(mi, Min(convert(varchar(10),ScanDate,8)), Max(convert(varchar(10),ScanDate,8)))%60 <= 0) then 
      '< 1 min' 
      else 
      '' 
     end) ProcessTime 

Answer 1

使用SQL Server 2005，您可以使用CTE来改进此查询。
类似这样的：

;WITH minmax AS 
(
    SELECT someKey, 
      DateDiff(mi, Min(convert(varchar(10),ScanDate,8)), Max(convert(varchar(10),ScanDate,8))) as mdiff 
    FROM tablename 
    GROUP BY fieldName 
) 
Select convert(varchar(10),ScanDate,101) as [Date], tmb.WO, tc.WOCategory, count(IdSingle) QtyProd, tb.Brand, tm.Model, 
     (case when (mdiff/60%24 > 0) then 
      convert(varchar(5), mdiff/60%24) + 'h '    
       else 
      '' 
     end) + 
     (case when (mdiff%60 > 0) then 
      convert(varchar(5), mdiff%60) + 'm'    
       else 
      '' 
     end) +   
     (case when 
      (DateDiff(mi, mdiff/60%24 <= 0) and (DateDiff(mi, mdiff%60 <= 0) then 
      '< 1 min' 
      else 
      '' 
     end) ProcessTime 
from tablename 
join minmax on tablename.somekey = minmax.somekey