SQL - 无效的查询，因为它不包含在聚合函数或组合子句中

问题描述：

我遇到了一些问题。我有3个表Product，Version,Date。SQL - 无效的查询，因为它不包含在聚合函数或组合子句中

依赖关系：

version.ProductId = Product.Id（有> = 1个版本的一个产品）
Date.VersionId = Version.ID（一对一）

我想对产品 - 版本，其中DateVersion是Max（对于每个产品）

类似于：

Product 1 - 1.0 
Product 2 - 0.9 etc

我试着用下面的查询：

SELECT 
    productName, versionName 
FROM 
    (SELECT 
     p.Name AS productName, v.Name AS versionName, MAX(d.Date) AS ddate 
    FROM 
     Product AS p 
    INNER JOIN 
     Version AS v ON v.ProductId = p.Id 
    INNER JOIN 
     Date AS d ON d.VersionId = v.Id 
    WHERE 
     (d.Date < { fn CURDATE() }) 
    GROUP BY p.Name) AS prd

但我得到一个错误：

Column Version.Name is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

如何处理这个问题？

答

您需要通过条款

SELECT productName, versionName 
FROM 
    (SELECT p.Name AS productName, v.Name AS versionName, MAX(d.Date) AS ddate 
    FROM Product AS p INNER JOIN Version AS v ON v.ProductId = p.Id 
    INNER JOIN VDate AS d ON d.VersionId = v.Id 
    WHERE (d.Date < { fn CURDATE() }) 
    GROUP BY p.Name, v.Name 
    ) AS prd

编辑包括v.Name在一组。

select 
product.name, 
version.name 
    from 
product 
    inner join 
     version 
    on product.id = version.productid 
inner join 
    date on version.id = date.versionid 
inner join 
(
    select productid, MAX(date) as maxdate 
    from version 
     inner join date on version.id = date.versionid 
    group by productid 
) maxver 
    on date.date = maxver.maxdate 
    and version.productid = maxver.productid

我想说，但我有很多对1种产品。喜欢：产品1 - 0.1，产品1 - 0.2，产品1 - 0.3等。 – 2012-07-30 09:39:18

啊，我明白了。请参阅编辑 – podiluska 2012-07-30 09:56:11

谢谢！编辑版本正常工作;） – 2012-07-30 10:25:28

答

试试这个

SELECT p.Name AS productName, v.Name AS versionName 
FROM Product AS p INNER JOIN Version AS v ON v.ProductId = p.Id 
inner join 
(select VersionId,MAX(d.Date) from VDate group by Versionid) AS d 
on d.VersionId = v.Id 
WHERE and d.Date < { fn CURDATE() } 
GROUP BY p.Name,v.name

SQL - 无效的查询，因为它不包含在聚合函数或组合子句中

相关推荐