GROUP BY子句导致错误(Group By clause causing error)

因此，这里的背景：发展新世纪的ASP.NET MVC 4 Web应用程序，我在我的数据库，由2个外键的表ProductAllocations：一个是从我的表产品，并从表人员其他。我还有一个表，车辆其中包含Products表的外键

我想选择一个产品（产品可以被分配一些时间）分组的分配和他们的信息。这里是我的存储过程：

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子句中产生的错误： 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. 我与Visual Studio 2010的工作。

我是新来的SQL所以我不知道发生了什么事情的想法。

Answer 1:

田野上你组的，你需要或者使用聚合sum ， max等，或您需要包括子句中的列，请访问以下链接： SQL集团通过与汇总值

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;

Answer 2:

当使用Group BY ，你应该把是否要在选择你的域Group By条款或你应该对他们使用聚合函数，例如

SELECT 

    pa.Id_Product,Min(p.FirstName) as Firstname,Min(p.LastName) as LastName

    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

你应该知道你想要什么聚合函数使用，也有许多最小值，最大值，总和...

欲了解更多信息，请阅读本GROUP BY（的Transact-SQL）