我如何解决旧版MySQL中缺少where子句子查询的问题？

I've got a table Foo with columns A, B, and C. What I need to get out of this is each distinct value of A, and the value of C in with the row that has the greatest B for that A.

我有一个带有A，B和C列的表Foo。我需要从中得到的是A的每个不同值，以及C in的值与A中具有最大B的行。

If I do this:

如果我这样做：

select A, max(B)
from Foo
group by A

That gets me the greatest B for each A, and I can run another query based on these results to get the values of C that I need. But I'd like to be able to do this in one query.

这让我成为每个A的最大B，我可以根据这些结果运行另一个查询，以获得我需要的C值。但我希望能够在一个查询中执行此操作。

One might suggest a subquery in the where clause, but the version of MySQL I'm using does not support that. I cannot update MySQL to a later version. Is there a way I can get what I need in a single query?

有人可能会在where子句中建议子查询，但我使用的MySQL版本不支持。我无法将MySQL更新到更高版本。有没有办法在单个查询中获得我需要的东西？

I am using MySQL 4.0.27.

我正在使用MySQL 4.0.27。

2 个解决方案

#1

So you want to to select those rows, for each A, that have the maximum B.
You're almost there -- you just need to join your query back to the original table:

因此，您希望为每个A选择那些具有最大B的行。您几乎就在那里 - 您只需将查询加入原始表：

SELECT Foo.* FROM Foo 
INNER JOIN (
    SELECT A, MAX(B) AS MAXB 
    FROM Foo 
    GROUP BY A
) AS Subtable -- your original query
ON Subtable.MAXB = Foo.B and Foo.A = Subtable.A;

Note: Since I don't know what version of MySQL you're using, I can't guarantee this will work on yours.

注意：由于我不知道您正在使用的MySQL版本，我不能保证这对您的版本有用。

Update: here are a couple of other examples of replacing subqueries with joins:

更新：以下是使用连接替换子查询的其他几个示例：

example 1
example 2

例1示例2

Second update: due to MySQL compatibility issues, this version moves the subquery, and adds an implicit join, putting the join conditions in a where-clause:

第二次更新：由于MySQL兼容性问题，此版本移动子查询，并添加隐式连接，将连接条件放在where子句中：

SELECT Foo.* 
FROM 
    Foo, 
    (SELECT A, MAX(B) AS MAXB 
        FROM Foo 
        GROUP BY A
    ) AS Subtable -- your original query
WHERE Subtable.MAXB = Foo.B and Foo.A = Subtable.A;

Source: according to the MySQL manual for 4.0 and 4.1, subqueries are allowed in FROM-clauses -- so hopefully this works on your system.

来源：根据4.0和4.1的MySQL手册，FROM-clauses中允许使用子查询 - 所以希望这适用于您的系统。

#2

Not sure if older MySQL has support for derived tables, but I would use something like this:

不确定较旧的MySQL是否支持派生表，但我会使用这样的东西：

SELECT t2.*
FROM (
    SELECT A, max(B) as B
    FROM Foo
    GROUP BY A
) t1
JOIN (
    SELECT A, max(B) as B, C
    FROM Foo
    GROUP BY A, C
) t2 ON t1.A = t2.A AND t1.B = t2.B

The first derived table will limit the second derived table to only the highest value B for each distinct A.

第一个派生表将第二个派生表限制为每个不同的A的最高值B.

#1