Oracle - 来自单个列的唯一值，但返回其他列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4891143/
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 - Unique values from a single column, but returning other columns
提问by dscl
I'm trying to figure out how pull select values from a table column, but return other results as well. For example say I have the following table
我试图弄清楚如何从表列中提取选择值，但也返回其他结果。例如说我有下表
person ..........
------------------
bob ..........
mary ..........
bob ..........
sue ..........
What I want to return back is
我要回的是
bob ..........
mary ..........
sue ..........
In this case I don't care what values I am actually receiving back for bob from the other columns how would I do that?
在这种情况下，我不在乎我实际上从其他列中为 bob 收到了什么值，我该怎么做？
Follow up question, what if I did care though? What if I had this table
跟进问题，如果我确实在乎怎么办？如果我有这张桌子怎么办
person type color ..........
----------------------------------
bob 0 blue ..........
mary 1 green ..........
bob 1 red ..........
sue 0 yellow ..........
And this time I again want unique people, but I want the results back for bob when his type is 0, so for him I would see
这一次我再次想要独特的人，但是当他的类型为 0 时，我想要返回给 bob 的结果，所以对于他我会看到
bob 0 blue ..........
mary 1 green ..........
sue 0 yellow ..........
Thanks!
谢谢！
回答by Justin Cave
You can use analytic functionslike RANK to describe the algorithm for which row you want to keep and then do something like
您可以使用RANK 之类的分析函数来描述要保留的行的算法，然后执行类似操作
SELECT *
FROM (SELECT person,
type,
color,
RANK() OVER( PARTITION BY person
ORDER BY type asc ) rnk
FROM <<person_table>>)
WHERE rnk = 1
This returns the "first" row for each person where "first" is determined by the row with the lowest TYPE value.
这将返回每个人的“第一”行，其中“第一”由具有最低 TYPE 值的行确定。
One thing to be aware of when you start looking at ranking rows is that there are three different analytic functions-- RANK, DENSE_RANK, and ROW_NUMBER-- that all do basically the same thing but handle ties differently. RANK is the standard athletics ranking function-- if there are two rows that tie for first place (i.e. two rows where PERSON='bob' and TYPE=0), they would bothget a rank of 1 while the next row would get a rank of 3 so there would be no "second place" row. In the same situation, DENSE_RANK would also give both tied rows a rank of 1 but would give the next row a rank of 2. ROW_NUMBER would arbitrarily give one of the tied rows a rank of 1, give the other one a rank of 2, and give the third row a rank of 3. Of course, you can break ties by adding additional columns to the ORDER BY clause.
当您开始查看排名行时要注意的一件事是，有三个不同的分析函数——RANK、DENSE_RANK 和 ROW_NUMBER——它们都做基本相同的事情，但处理关系的方式不同。RANK是标准的田径排名function--如果有两排并列第一（即两排，其中PERSON =“鲍勃”和TYPE = 0），他们将都得到的秩为1，而下一行会得到一个排名 3，因此不会有“第二名”行。在同样的情况下，DENSE_RANK 也会给两个并列行一个等级 1，但会给下一行一个等级 2。 ROW_NUMBER 会任意给一个绑定行一个等级 1，给另一个等级 2，并给第三行一个等级 3。当然，
回答by Conrad Frix
Alternative syntax using Generic SQL for DBs that don't have RANK
对没有 RANK 的数据库使用通用 SQL 的替代语法
SELECT
person,
type,
color
FROM
<<person_table>> p
inner join
(SELECT person,
min(type) type
FROM <<person_table>>
GROUP BY
person) minType
on p.Person = minType.Person
and p.type = minType.type
