按列分组，并依赖于另一个列 [英] Grouping by Column with Dependence on another Column

问题描述

这里是我试图通过MySQL查询彻底解决的问题的简化视图.这不是我要处理的实际表.

Here is a simplified look at the problem I am trying to cleanly solve via a MySQL query. This is not the actual table I am dealing with.

如果我有下表:

Name Buyer ID  
John Fred  4  
John Smith 3  
Fred Sally 2  
John Kelly 1

我希望查询返回以下内容:

I would like a query to return the following:

Name Buyer ID      
John Fred  4  
Fred Sally 2  

我们按名称"分组并显示最新的行/购买者/ID.

Such that we group by 'name' and show the latest row / buyer / ID.

我试图通过执行嵌套的select语句来实现此目的，其中首先执行"ORDER BY ID DESC"，然后在最外面的SELECT上执行"GROUP BY NAME".并且，尽管这是解决问题的一种round回方式，但似乎通过订购，可以将正确的选择返回给我.不幸的是，"GROUP BY"不能保证"买方"列将包含预期的条目.

I tried to implement this by performing a nested select statement, wherein I first performed "ORDER BY ID DESC" then, on the outermost SELECT, "GROUP BY NAME". And, while this is a roundabout way of solving the problem, it seemed that, by virtue of the ordering, the correct selection would be returned to me. Unfortunately, "GROUP BY" does not 'guarantee' that the 'Buyer' column will contain the expected entry.

对于将其实现为查询有什么有用的建议吗?目前，我在大型表转储上运行的查询效率极低的PHP版本"-绝对不是最佳选择.

Any helpful suggests for implementing this as a query? At the moment, I have a highly-inefficient PHP 'version' of the query running on a large table dump - definitely not the best choice.

推荐答案

尝试一下，子查询背后的想法是，它使用MAX( aggregate函数)为每个Name获取最新的ID ).然后将它与子查询的两列上的表本身连接起来.

Try this one, the idea behind the subquery is that it gets the latest ID for each Name using MAX (aggregate function). Then join it against the table itself on the two columns of the subquery.

SELECT  a.*
FROM    tableName a
        INNER JOIN 
        (
            SELECT name, MAX(ID) maxID
            FROM tableName
            GROUP BY name
        ) b ON a.Name = b.Name AND
                a.ID = b.MaxID

• SQLFiddle演示
• SQLFiddle Demo

这篇关于按列分组，并依赖于另一个列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！