迁移到SQL Server 2012时，MySQL GROUP BY不起作用

问题描述

我将我的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

如果我将查询更改为:

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

然后我收到此错误:

有什么想法吗?

这是表格的设计视图:

推荐答案

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不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！