如何使用 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 行

如果您只需要 一行 具有 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 

如果性能很重要，请确保在 table_a (date_1, integer_2) 上有一个索引。

Answer 2

SELECT a.INTEGER_0, a.INTEGER_1, a.DATE_0, a.DATE_1, MAX(a.INTEGER_2) AS MaxInt2
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
GROUP BY a.INTEGER_0, a.INTEGER_1, a.DATE_0, a.DATE_1
HAVING MAX(a.INTEGER_2) = 42 /* Adjust this according to what your filter needs */

Answer 3

您尚未指定您使用的 PostgreSQL 版本。如果是 8.4+，您可以尝试不同的方法并使用 排名函数以实现您的目标：

WITH ranked AS (
  SELECT
    INTEGER_0,
    INTEGER_1,
    DATE_0,
    DATE_1,
    INTEGER_2,
    RANK() OVER (
      PARTITION BY
        INTEGER_0,
        INTEGER_1,
        DATE_0
      ORDER BY
        DATE_1 DESC
    ) AS rnk
  FROM TABLE_A
)
SELECT
  INTEGER_0,
  INTEGER_1,
  DATE_0,
  DATE_1,
  INTEGER_2
FROM TABLE_A
WHERE rnk = 1
ORDER BY INTEGER_2 DESC
LIMIT 1

Answer 4

SET search_path='tmp';
-- generate some data
DROP TABLE atable CASCADE;
CREATE TABLE atable
    ( integer_0 INTEGER
    , integer_1 INTEGER
    , date_0 DATE
    , date_1 DATE
    , integer_2 INTEGER
    );  
INSERT INTO atable( integer_0,integer_1,date_0,date_1,integer_2)
VALUES
(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)
    ;

-- Query the data
SELECT integer_0,integer_1,date_0,date_1,integer_2
FROM atable a0
WHERE NOT EXISTS (SELECT *
    FROM atable a1
    WHERE a1.integer_0 = a0.integer_0
    AND a1.integer_1 = a0.integer_1
    AND a1.date_0 = a0.date_0
    AND a1.date_1 > a0.date_1
    )   
AND NOT EXISTS (SELECT *
    FROM atable a2
    WHERE a2.integer_0 = a0.integer_0
    AND a2.integer_1 = a0.integer_1
    AND a2.date_0 = a0.date_0
    AND a2.date_1 = a0.date_1
    AND a2.integer_2 > a0.integer_2
    )   
    ;