group by 分组后，需要查询其他字段的几种sql性能比较

最新推荐文章于 2024-03-28 20:24:12 发布

IT杂人

最新推荐文章于 2024-03-28 20:24:12 发布

阅读量680

点赞数

文章标签： sql sqlserver mysql

本文链接：https://blog.csdn.net/atzqtzq/article/details/108752799

版权

使用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