使用100万单表测试结果,第一种0.7秒,第二种要2秒以上,第三种1秒以上
还是在group by后面加字段性能高
如下测试sql:
1.直接在group by 后面加字段
SELECT
[m].[MasterID] AS [MasterID],
[m].[HeadImage] AS [HeadImage],
[m].[MarginAmount] AS [MarginAmount],
[m].[Profield_txt] AS [Profield_Txt],
[m].[ApproveStatus] AS [ApproveStatus],
[m].[ReceiveOrdercount] AS [OrderCount],
[m].[OverallScore] AS [OverallScore],
[m].[ApplauseRate] AS [ApplauseRate],
[m].[IsExam] AS [IsExam],
[m].[ExamScore] AS [ExamScore],
[m].[Grade] AS [Grade],
[m].[Degree] AS [Degree],
[m].[ServiceSupport] AS [ServiceSupport],
COUNT ([ulr].[ID]) AS [LocationCount],
MAX ([ulr].[ReportTime]) AS [LastLocationTime]
FROM [User_LBSRecord] ulr
INNER JOIN [Master] m ON [ulr].[UserID] = [m].[MasterID]
where ReportTime>'2020-08-05'
GROUP BY
[m].[MasterID],
[m].[Name],
[m].[HeadImage],
[m].[MarginAmount],
[m].[Profield_txt],
[m].[ApproveStatus],
[m].[ReceiveOrdercount],
[m].[IsExam],
[m].[ExamScore],
[m].[OverallScore],
[m].[ApplauseRate],
[m].[Grade],
[m].[Degree],
[m].[ServiceSupport]
2.在select 后面使用聚合函数
SELECT
[m].[MasterID] AS [MasterID],
MAX([m].[HeadImage]) AS [HeadImage],
MAX([m].[MarginAmount]) AS [MarginAmount],
MAX([m].[Profield_txt]) AS [Profield_Tx],
MAX([m].[ApproveStatus]) AS [ApproveStatus],
MAX([m].[ReceiveOrdercount]) AS [OrderCount],
MAX([m].[OverallScore]) AS [OverallScore],
MAX([m].[ApplauseRate]) AS [ApplauseRate],
MAX([m].[IsExam]) AS [IsExam],
MAX([m].[ExamScore]) AS [ExamScore],
MAX([m].[Grade]) AS [Grade],
MAX([m].[Degree]) AS [Degree],
MAX([m].[ServiceSupport]) AS [ServiceSupport],
COUNT ([ulr].[ID]) AS [LocationCount],
MAX ([ulr].[ReportTime]) AS [LastLocationTime]
FROM [User_LBSRecord] ulr
INNER JOIN [Master] m ON [ulr].[UserID] = [m].[MasterID]
where ReportTime>'2020-08-05'
GROUP BY
[m].[MasterID]
3.在select后面使用子查询
SELECT
ulr.UserID,
COUNT ([ulr].[ID]) AS [LocationCount],
MAX ([ulr].[ReportTime]) AS [LastLocationTime],
(select phone from master m where m.masterid=ulr.userid),
(select phone from master m where m.masterid=ulr.userid),
(select name from master m where m.masterid=ulr.userid),
(select ApplauseRate from master m where m.masterid=ulr.userid),
(select IsExam from master m where m.masterid=ulr.userid),
(select ExamScore from master m where m.masterid=ulr.userid),
(select Grade from master m where m.masterid=ulr.userid)
FROM [User_LBSRecord] ulr
where ReportTime>'2020-08-05'
GROUP BY
ulr.UserID