Oracle 11g SQL 在多列查询的一列中获取唯一值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/983202/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle 11g SQL to get unique values in one column of a multi-column query
提问by Ian Cohen
Given a table Aof people, their native language, and other columns C3 .. C10 represented by ...
给定一个表A的人、他们的母语和其他列 C3 .. C10 用...表示
Table A
表A
PERSON LANGUAGE ... bob english john english vlad russian olga russian jose spanish
How do I construct a query which selects all columns of one row for each distinct language?
如何构建一个查询，为每种不同的语言选择一行的所有列？
Desired Result
想要的结果
PERSON LANGUAGE ... bob english vlad russian jose spanish
It doesn't matter to me which row of each distinct language makes the result. In the result above, I chose the lowest row number of each language.
对我来说，每种不同语言的哪一行产生结果并不重要。在上面的结果中，我选择了每种语言的最低行号。
回答by Joe
Eric Petroelje almost has it right:
Eric Petroelje 几乎是对的：
SELECT * FROM TableA
WHERE ROWID IN ( SELECT MAX(ROWID) FROM TableA GROUP BY Language )
Note: using ROWID (row unique id), not ROWNUM (which gives the row number within the result set)
注意：使用 ROWID（行唯一 ID），而不是 ROWNUM（给出结果集中的行号）
回答by Jeffrey Kemp
This will be more efficient, plus you have control over the ordering it uses to pick a value:
这将更有效，而且您可以控制它用来选择值的顺序：
SELECT DISTINCT
FIRST_VALUE(person)
OVER(PARTITION BY language
ORDER BY person)
,language
FROM tableA;
If you really don't care which person is picked for each language, you can omit the ORDER BY clause:
如果您真的不在乎每种语言选择了哪个人，则可以省略 ORDER BY 子句：
SELECT DISTINCT
FIRST_VALUE(person)
OVER(PARTITION BY language)
,language
FROM tableA;
回答by Eric Petroelje
My Oracle is a bit rusty, but I think this would work:
我的 Oracle 有点生疏，但我认为这会起作用：
SELECT * FROM TableA
WHERE ROWID IN ( SELECT MAX(ROWID) FROM TableA GROUP BY Language )
回答by Harper Shelby
I'd use the RANK() function in a subselect and then just pull the row where rank = 1.
我会在子选择中使用 RANK() 函数，然后只拉出 rank = 1 的行。
select person, language
from
(
select person, language, rank() over(order by language) as rank
from table A
group by person, language
)
where rank = 1
回答by Scott Swank
For efficiency's sake you want to only hit the data once, as Harper does. However you don't want to use rank() because it will give you ties and further you want to group by language rather than order by language. From there you want add an order by clause to distinguish between rows, but you don't want to actually sort the data. To achieve this I would use "order by null" E.g.
为了效率起见，您只想像 Harper 那样只访问一次数据。但是，您不想使用 rank() ，因为它会给您带来联系，并且您还想按语言分组而不是按语言排序。从那里你想添加一个 order by 子句来区分行，但你不想实际对数据进行排序。为了实现这一点，我将使用“order by null”，例如
count(*) over (group by language order by null)
count(*) over（按语言顺序分组按空）
回答by Phil
select person, language
from table A
group by person, language
will return unique rows
将返回唯一的行
