令人困惑的 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

大多数数据库将支持在派生表/内联视图上使用自联接：

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 记录具有相同的时间戳值，这将返回重复项。

Answer 2

如果您希望每个水果名称占一行，则必须按水果名称分组。

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

你想如何处理抢七是一个单独的问题。

Answer 3

尝试子查询：

select a.fruit_name, a.fruit_id, a.fruit_owner
from my_fruits a
where a.fruit_timestamp =
 (select max(b.fruit_timestamp)
  from my_fruits b
  where b.fruit_id = a.fruit_id)

Answer 4

我会通过找出您感兴趣的（fruit_name，fruit_timestamp）列表，然后将该“表”与实际的水果表分组并检索其他值来做到这一点。

SELECT fruit_and_max_t.fruit_name, 
       my_fruits.fruit_id,
       my_fruits.fruit_owner
FROM my_fruits, 
  ( SELECT fruit_name, MAX(fruit_timestamp) AS max_timestamp
    FROM my_fruits
    GROUP BY fruit_name) AS fruit_and_max_t,
WHERE fruit_and_max_t.max_timestamp = my_fruits.fruit_timestamp
  AND fruit_and_max_t.fruit_name    = my_fruits.fruit_name

这假设表中不存在具有相同值（fruit_name，fruit_timestamp）的多个条目，即元组（对）充当唯一标识符。