Group By 子句导致错误

本文介绍了Group By 子句导致错误的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

所以这里是上下文:开发 ASP.NET MVC 4 Web 应用程序，我的数据库中有一个表 ProductAllocations，它由 2 个外键组成:一个来自我的表 Products 和表中的另一个人.我有另一个表 Vehicles，其中包含表 Products

So here is the context : Developping an ASP.NET MVC 4 web app, I have in my database a table ProductAllocations which is composed of 2 foreign keys : one from my table Products and the other from the table Persons. I have another table, Vehicles which contains a foreign key of the table Products

我想选择按产品分组的分配及其信息(一个产品可以多次分配).这是我的存储过程:

I want to select the allocations and their information grouped by a product (a product can be allocated several time). Here is my stored procedure :

ALTER PROCEDURE GetAllVehicles AS BEGIN SET NOCOUNT ON SELECT p.FirstName, p.LastName, pa.EndDate, pr.PurchaseDate, pr.SerialNumber, pr.CatalogPrice, v.PlateNumber, v.FirstCirculationDate, V.FirstDrivingTax, v.UsualDrivingTax FROM bm_ProductAllocations AS pa INNER JOIN bm_Persons AS p ON pa.Id_Person = p.Id_Person INNER JOIN bm_Products AS pr ON pa.Id_Product = pr.Id_Product INNER JOIN bm_Vehicles AS v ON pr.Id_Product = v.Id_Product GROUP BY pa.Id_Product END

但是，Group By 子句生成错误:列'bm_Persons.FirstName' 在选择列表中无效，因为它既没有包含在聚合函数中，也没有包含在 GROUP BY 子句中. I正在使用 Visual Studio 2010.

However, the Group By clause is generating an error : Column 'bm_Persons.FirstName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. I'm working with Visual Studio 2010.

我是 SQL 新手，所以我不知道发生了什么.

I'm new to SQL so I have no idea about what's going on.

推荐答案

字段你分组，你需要使用聚合sumcode>、max 等或者您需要在子句中包含列，请参阅以下链接:SQL 分组依据 &总结价值

The fields you group by, you need to either use an aggregation sum, max etc or you need to include the columns in the clause, see the following links: SQL Group By & summarizing values

SELECT p.FirstName ,p.LastName ,pa.EndDate ,pr.PurchaseDate ,pr.SerialNumber ,pr.CatalogPrice ,v.PlateNumber ,v.FirstCirculationDate ,v.FirstDrivingTax ,v.UsualDrivingTax FROM bm_ProductAllocations AS pa INNER JOIN bm_Persons AS p ON pa.Id_Person = p.Id_Person INNER JOIN bm_Products AS pr ON pa.Id_Product = pr.Id_Product INNER JOIN bm_Vehicles AS v ON pr.Id_Product = v.Id_Product GROUP BY pa.Id_Product ,p.FirstName ,p.LastName ,pa.EndDate ,pr.PurchaseDate ,pr.SerialNumber ,pr.CatalogPrice ,v.PlateNumber ,v.FirstCirculationDate ,v.FirstDrivingTax ,v.UsualDrivingTax;