SQL数据库--统计类型查询

在做统计类型的查询或数据获取时，应该先按照统计的类别先进行分组，然后查询。

ALTER PROCEDURE [dbo].[Workload_GetEmployeeWorkload]
(
@DepartmentId    int ,
@StartTime    datetime,
@EndTime    datetime
)
AS
SELECT 
  HumanResource_Records.RecordId,
  dbo.getEmployeeName(HumanResource_Records.RecordId)  as  Employee,
  dbo.getSumWorkHours(HumanResource_Records.RecordId, 44 ,@StartTime,@EndTime)  as  WorkHours,
  dbo.getSumAvailableOverTimeHours(HumanResource_Records.RecordId, 44 ,@StartTime,@EndTime)  as  OverTimeHours
FROM
  HumanResource_Records,
  Project_EntryLog
WHERE 
  HumanResource_Records.DepartmentId = @DepartmentId
AND  HumanResource_Records.RecordId = Project_EntryLog.RecordID
AND  Project_EntryLog.WorkDate >= @StartTime
AND  Project_EntryLog.WorkDate <= @EndTime
AND  Project_EntryLog.ReportStatusID != 0

以上代码当库中有7000多条记录时，获取数据用时16秒

修改存储过程如下：

ALTER PROCEDURE [dbo].[Workload_GetEmployeeWorkload]
(
@DepartmentId    int ,
@StartTime    datetime,
@EndTime    datetime
)
AS
SELECT 
      HumanResource_Records.RecordId,
     dbo.getEmployeeName(HumanResource_Records.RecordId)  as  Employee,
     dbo.getSumWorkHours(HumanResource_Records.RecordId, 44 ,@StartTime,@EndTime)  as  WorkHours,
     dbo.getSumAvailableOverTimeHours(HumanResource_Records.RecordId, 44 ,@StartTime,@EndTime)  as  OverTimeHours
FROM
    HumanResource_Records,
    Project_EntryLog
WHERE 
            HumanResource_Records.DepartmentId = @DepartmentId
AND   HumanResource_Records.RecordId = Project_EntryLog.RecordID
AND   Project_EntryLog.WorkDate >= @StartTime
AND   Project_EntryLog.WorkDate <= @EndTime
AND   Project_EntryLog.ReportStatusID != 0

group by HumanResource_Records.RecordId

仅仅增加了一句group by HumanResource_Records.RecordId，但耗费时间却近于0。
在用group by分组后，会先按照RecordId分组，然后查询，每个RecordId只查询一次，而不分组查询时却是只要记录中有的RecordId就进行查询，耗费更多的时间。当然使用group by也要看情况，不是任何情况下都适用，要具体情况具体分析了。

转载于:https://www.cnblogs.com/freekee/archive/2006/10/31/545798.html