SQL Server带INNER JOIN的查询添加Group By子句报错求助

你遇到的报错是SQL的标准语法约束：使用GROUP BY进行分组统计时，SELECT子句中所有未使用聚合函数的列，必须全部包含在GROUP BY子句中，否则数据库无法确定分组后非分组列的取值逻辑。

以下是不同时间维度的分组统计实现方案：

按日分组统计示例 #

SELECT 
  CAST(p.[E3TimeStamp] AS DATE) AS '统计日期',
  p.Maquina As 'Maquina Linha',
  e.Maquina As 'Maquina Escolha',
  COUNT(*) AS '当日记录数' -- 可根据实际需求替换为SUM/AVG等其他聚合函数
FROM [E3_GERAL].[dbo].[Tab_Dados_Producao] p
INNER JOIN [E3_GERAL].[dbo].[Tab_Dados_Escolha] e 
  ON p.[E3TimeStamp] = e.[E3TimeStamp]
GROUP BY 
  CAST(p.[E3TimeStamp] AS DATE),
  p.Maquina,
  e.Maquina

按月分组统计示例 #

SELECT 
  DATEFROMPARTS(YEAR(p.[E3TimeStamp]), MONTH(p.[E3TimeStamp]), 1) AS '统计月份',
  p.Maquina As 'Maquina Linha',
  e.Maquina As 'Maquina Escolha',
  COUNT(*) AS '当月记录数'
FROM [E3_GERAL].[dbo].[Tab_Dados_Producao] p
INNER JOIN [E3_GERAL].[dbo].[Tab_Dados_Escolha] e 
  ON p.[E3TimeStamp] = e.[E3TimeStamp]
GROUP BY 
  DATEFROMPARTS(YEAR(p.[E3TimeStamp]), MONTH(p.[E3TimeStamp]), 1),
  p.Maquina,
  e.Maquina

按年分组统计示例 #

SELECT 
  YEAR(p.[E3TimeStamp]) AS '统计年份',
  p.Maquina As 'Maquina Linha',
  e.Maquina As 'Maquina Escolha',
  COUNT(*) AS '当年记录数'
FROM [E3_GERAL].[dbo].[Tab_Dados_Producao] p
INNER JOIN [E3_GERAL].[dbo].[Tab_Dados_Escolha] e 
  ON p.[E3TimeStamp] = e.[E3TimeStamp]
GROUP BY 
  YEAR(p.[E3TimeStamp]),
  p.Maquina,
  e.Maquina

如果不需要将Maquina Linha和Maquina Escolha作为分组维度，可直接将两列从SELECT和GROUP BY子句中移除，或对两列使用MAX()/MIN()等聚合函数包裹即可正常执行。

内容的提问来源于stack exchange，提问作者Vitor Marques Lourenço