sql - 如何使用 MAX 函数过滤 INNER JOIN 的结果

Question

我需要在这个查询的结果中添加一个带有 MAX 函数的过滤器；

SELECT a.INTEGER_0, a.INTEGER_1, a.DATE_0, a.DATE_1, a.INTEGER_2
FROM TABLE_A a           
INNER JOIN               
   (SELECT b.INTEGER_0, b.INTEGER_1, b.DATE_0, max(b.DATE_1) AS max_date
    FROM TABLE_A b     
    GROUP BY b.INTEGER_0, b.INTEGER_1, b.DATE_0
   ) AS result         
ON  a.INTEGER_0 = b.INTEGER_0
AND a.INTEGER_1 = b.INTEGER_1
AND a.DATE_0 = b.DATE_0  
AND a.DATE_1 = b.max_date

没关系!!但我需要用 max(INTEGER_2) 过滤结果。
我尝试了另一个 INNER JOIN，但结果很糟糕!

附加信息
行:

1,7,'2011-02-01','2011-01-01',8
1,7,'2011-02-01','2011-01-02',7
1,7,'2011-02-01','2011-01-04',6
1,7,'2011-02-01','2011-01-04',3
1,7,'2011-02-01','2011-01-04',3

正确结果:

1,7,'2011-02-01','2011-01-04',6

Answer 1

首先，您的查询中有一个语法错误:子查询的别名是AS result。您将它与 b 的内部别名混淆了。

1行

如果您只想要 one 行 max(integer_2) 那么 ORDER BY/LIMIT 将执行工作。您的查询可能如下所示:

SELECT a.integer_0, a.integer_1, a.date_0, a.date_1, a.integer_2
FROM   table_a a           
JOIN   (
    SELECT b.integer_0, b.integer_1, b.date_0, max(b.date_1) as max_date
    FROM   table_a b     
    GROUP  BY b.integer_0, b.integer_1, b.date_0
    ) AS b ON a.integer_0 = b.integer_0
        AND a.integer_1 = b.integer_1
        AND a.date_0 = b.date_0  
        AND a.date_1 = b.max_date
ORDER  BY a.integer_2 DESC
LIMIT  1;

所有行

如果您希望结果集的所有行具有max(integer_2)(正如您的查询认为暗示的那样)，那么您可以这样做:

SELECT a.integer_0, a.integer_1, a.date_0, a.date_1, a.integer_2
FROM   table_a a           
JOIN   (
    SELECT b.integer_0, b.integer_1, b.date_0, max(b.date_1) as max_date
    FROM   table_a b     
    GROUP  BY b.integer_0, b.integer_1, b.date_0
    ) AS b ON a.integer_0 = b.integer_0
        AND a.integer_1 = b.integer_1
        AND a.date_0 = b.date_0  
        AND a.date_1 = b.max_date
WHERE (a.date_1, a.integer_2) = (
        SELECT date_1, integer_2
        FROM   table_a
        ORDER  BY 1 DESC, 2 DESC
        LIMIT  1);

或者更好的是，大大简化为:

SELECT integer_0, integer_1, date_0, date_1, integer_2
FROM   table_a a           
WHERE     (integer_0, integer_1, date_0, date_1, integer_2) = ( 
    SELECT integer_0, integer_1, date_0, date_1, integer_2
    FROM   table_a b
    ORDER  BY 4 DESC, 5 DESC
    LIMIT  1);
-- ORDER  BY something?  -- add these lines ..
-- LIMIT  1;            -- .. if you want just one row 

或者再简化一些

SELECT *
FROM   table_a a           
WHERE  (a) = ( 
    SELECT b
    FROM   table_a b
    ORDER  BY date_1 DESC, integer_2 DESC
    LIMIT  1);
-- ORDER  BY something?  -- add these lines ..
-- LIMIT  1;            -- .. if you want just one row 

如果性能很重要，请务必在表_a (date_1, integer_2) 上有一个索引。