迁移到SQL Server 2012时，MySQL GROUP BY不起作用 [英] MySQL GROUP BY doesn't work when migrated to SQL Server 2012

问题描述

我将我的Delphi应用程序从MySQL迁移到SQL Server2012.在MySQL中，我有以下查询:

I'm moving my Delphi application from MySQL to SQL server 2012. In MySQL I had this query:

SELECT *,(XS+S+M+L+XL+XXL+[1Size]+Custom) as Total FROM StockData  
GROUP BY StyleNr,Customer,Color  
ORDER BY StyleNr,Customer,Color

效果很好.但是在Microsoft SQL Server 2012中，该查询显示为

And it worked perfectly. But in Microsoft SQL Server 2012 this query says

消息8120，第16级，状态1，第1行
选择列表中的"StockData.ID"列无效，因为它既不包含在聚合函数中，也不包含在GROUP BY子句中.

Msg 8120, Level 16, State 1, Line 1
Column 'StockData.ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

如果我将查询更改为:

SELECT *,([XS]+[S]+[M]+[L]+[XL]+[XXL]+[1Size]+[Custom])  total
 FROM [dbo].[stockdata]
 GROUP BY ID,StyleNr,Customer,Color
 ORDER BY StyleNr,Customer,Color

然后我收到此错误:

消息8120，第16级，状态1，第1行
选择列表中的'dbo.stockdata.XS'列无效，因为它既不包含在聚合函数中，也不包含在GROUP BY子句中.

Msg 8120, Level 16, State 1, Line 1
Column 'dbo.stockdata.XS' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

有什么想法吗?

这是表格的设计视图:

推荐答案

SQL Server正常工作.您必须将SELECT列表中的所有项目都包含在GROUP BY或聚合函数中:

SQL Server is working as expected. You must include all items in your SELECT list in either a GROUP BY or in an aggregate function:

SELECT *,(XS+S+M+L+XL+XXL+[1Size]+Custom) as Total 
FROM StockData  
-- GROUP BY ID,StyleNr,Customer,Color, XS,S,M,L,XL,XXL,[1Size],Custom
ORDER BY StyleNr,Customer,Color

或者您可以使用:

SELECT StyleNr,Customer,Color, SUM(XS+S+M+L+XL+XXL+[1Size]+Custom) as Total 
FROM StockData  
GROUP BY StyleNr,Customer,Color
ORDER BY StyleNr,Customer,Color;

这篇关于迁移到SQL Server 2012时，MySQL GROUP BY不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！