使用列的最大值从Oracle SQL表进行过滤行

Question

我正在学习Oracle SQL，我有一个如下的表：

+--------+--------+------------------------+
| name   | nation | count                  |
+--------+--------+------------------------+
| Ruben  | UK     | 2                      |
| Ruben  | EEUU   | 16                     |
| Cesar  | UK     | 21                     |
| Cesar  | EEUU   | 12                     |
| Cesar  | FRANCE | 4                      |
| John   | FRANCE | 3                      |
| John   | UK     | 7                      |
| ....   | ..     | ..                     |
+--------+--------+------------------------+

上表表示我使用查询创建的内联视图。如您所见，桌子组逐个名称和国家，并做一些计数。我想使用计数列过滤表，以具有这样的内容：

+--------+--------+
| name   | nation |
+--------+--------+
| Ruben  | EEUU   |
| Cesar  | UK     |
| John   | UK     |
| ....   | ..     |
+--------+--------+  

您可以看到，对于我想根据计数选择国家的每个名称。

Answer 1

使用keep分析关键字：

select name, min(nation) keep (dense_rank last order by cnt)
from (select name, nation, count(*) as cnt
      from /* your data source */
      group by name, nation)
group by name

1. min(nation)-最小在这种情况下是毫无意义的，但是你必须保留它（没有）
2. keep-仅保留nation的结果
3. dense_rank last说要拿起最后一个元素
4. order by cnt说如何定义元素的顺序

最后，它将为所有名称提供最大的数字。可以通过

来实现相同的结果

select name, min(nation) keep (dense_rank first order by cnt desc)

Answer 2

您可以使用其他级别的内部查询来执行此操作。类似：

select name, nation
from (
  select name, nation, cnt,
    row_number() over (partition by name, nation order by cnt desc) as rn
  from (
    select name, nation, count(*) as cnt
    from <your table>
  )
)
where rn = 1;

row_number() Analytic将一个伪列添加到最内向的查询中，该查询对每个名称/国家的最高数字排名为1；然后最外面的查询仅查看排名第一的查询。

您需要决定如何处理关系 - 如果两个国家/名称行具有相同的计数，则可以通过使用rank()而不是row_number()来显示这两者;或者，您可以通过将另一列添加到order by子句中来决定选择哪一个。目前，如果有两个相同的话，它只会向您展示其中之一 - 它显示的是任意的。