如何从Oracle SQL的几列中选择唯一值? [英] How can I select unique values from several columns in Oracle SQL?

问题描述

基本上，我有下表:

ID | Amount
AA | 10
AA | 20
BB | 30
BB | 40
CC | 10
CC | 50
DD | 20
DD | 60
EE | 30
EE | 70

我需要在每一列中获得唯一的条目，如以下示例所示:

I need to get unique entries in each column as in following example:

ID | Amount
AA | 10
BB | 30
CC | 50
DD | 60
EE | 70

到目前为止，下面的代码片段几乎提供了我想要的内容，但是first_value()可能会返回一些值，这在当前列中不是唯一的:

So far following snippet gives almost what I wanted, but first_value() may return some value, which isn't unique in current column:

first_value(Amount) over (partition by ID)

Distinct也不起作用，因为它返回唯一的行，而不是其值

Distinct also isn't helpful, as it returns unique rows, not its values

选择顺序无关紧要

推荐答案

即使Dimitri提到了有问题的组合，这也对我有用.我不知道这对于大容量有多快

This works for me, even with the problematic combinations mentioned by Dimitri. I don't know how fast that is for larger volumes though

with ids as (
  select id, row_number() over (order by id) as rn
  from data
  group by id
), amounts as (
  select amount, row_number() over (order by amount) as rn
  from data
  group by amount
)
select i.id, a.amount
from ids i
  join amounts a on i.rn = a.rn;

SQLFiddle当前不适用于我，这是我的测试脚本:

SQLFiddle currently doesn't work for me, here is my test script:

create table data (id varchar(10), amount integer);

insert into data values ('AA',10);
insert into data values ('AA',20);
insert into data values ('BB',30);
insert into data values ('BB',40);
insert into data values ('CC',10);
insert into data values ('CC',50);
insert into data values ('DD',20);
insert into data values ('DD',60);
insert into data values ('EE',30);
insert into data values ('EE',70);

输出:

id | amount
---+-------
AA |     10
BB |     20
CC |     30
DD |     40
EE |     50

这篇关于如何从Oracle SQL的几列中选择唯一值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！