Group By 子句导致错误 [英] Group By clause causing error

问题描述

所以这里是上下文:开发 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;

这篇关于Group By 子句导致错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！