sql - 令人困惑的 SELECT 语句

Question

首先，我将向您展示我的问题所涉及的示例表，然后我将提出问题。

[my_fruits]
fruit_name   |   fruit_id   | fruit_owner   |   fruit_timestamp
----------------------------------------------------------------
Banana       |   3          |  Timmy        |   3/4/11
Banana       |   3          |  Timmy        |   4/1/11
Banana       |   8          |  Timmy        |   5/2/11
Apple        |   4          |  Timmy        |   2/1/11
Apple        |   4          |  Roger        |   3/4/11

现在我想运行一个仅选择 fruit_name、fruit_id 和 fruit_owner 值的查询。我只想让每个水果排成一排，我希望它的决定方式是最新的时间戳。例如，对该表的完美查询将返回:

[my_fruits]
fruit_name   |   fruit_id   | fruit_owner   |
----------------------------------------------
Banana       |   8          |  Timmy        |
Apple        |   4          |  Roger        | 

我试过查询:

select max(my_fruits.fruit_name) keep 
    (dense_rank last order by my_fruits.fruit_timestamp) fruit_name,
       my_fruits.fruit_id, my_fruits.fruit_owner 
from my_fruits 
group by my_fruits.fruit_id, my_fruits.fruit_owner

现在的问题是返回基本上不同的水果名称、水果 ID 和水果所有者。

Answer 1

对于 Oracle 9i+，使用:

SELECT x.fruit_name,
       x.fruit_id,
       x.fruit_owner
  FROM (SELECT mf.fruit_name,
               mf.fruit_id,
               mf.fruit_owner,
               ROW_NUMBER() OVER (PARTITION BY mf.fruit_name
                                      ORDER BY mf.fruit_timestamp) AS rank
          FROM MY_FRUIT mf) x
 WHERE x.rank = 1

大多数数据库都支持在派生表/内联 View 上使用自连接:

SELECT x.fruit_name,
       x.fruit_id,
       x.fruit_owner
  FROM MY_FRUIT x
  JOIN (SELECT t.fruit_name,
               MAX(t.fruit_timestamp) AS max_ts
          FROM MY_FRUIT t
      GROUP BY t.fruit_name) y ON y.fruit_name = x.fruit_name
                              AND y.max_ts = x.fruit_timestamp

但是，如果有超过 2 个具有相同时间戳值的 fruit_name 记录，这将返回重复项。