如何在Oracle查询中组合SELECT DISTINCT和ROWNUM

Question

如果可能的话，我需要将下面的两个MySQL语句合并为一个ORACLE查询。

初始查询是

SELECT DISTINCT FIRST_NAME FROM PEOPLE WHERE LAST_NAME IN ("Smith","Jones","Gupta")

然后根据每个FIRST_NAME返回I查询

SELECT * 
FROM PEOPLE 
WHERE FIRST_NAME = {FIRST_NAME} 
  AND LAST_NAME IN ("Smith","Jones","Gupta") 
ORDER BY FIELD(LAST_NAME, "Smith","Jones","Gupta") DESC 
LIMIT 1

"List of last names"用作"default/override"指示符，因此每个名字只有一个人，如果存在多个相同名字的行，则只使用"last"列表中的last匹配。

我需要一个SQL查询，根据in (a,b,c)中值的顺序从"in"子句返回最后一行。下面是一个示例表，以及我需要从查询中得到的结果。

表PEOPLE，值

LAST_NAME    FIRST_NAME
.....
Smith        Mike    
Smith        Betty
Smith        Jane
Jones        Mike
Jones        Sally
....

我需要一个基于DISTINCT FIRST_NAME和LAST_NAME IN ('Smith'，'Jones')的查询，返回

Betty Smith
Jane Smith
Mike Jones
Sally Jones

Answer 1

你可以这样做:

select first_name, last_name
from (
  select p.first_name,
         p.last_name,
         row_number() over (partition by p.first_name
                            order by case p.last_name
                                     when 'Smith' then 1
                                     when 'Jones' then 2
                                     when 'Gupta' then 3
                                     end desc) as rn
  from people p
  where p.last_name in ('Smith','Jones','Gupta')
)
where rn = 1;

演示:SQL Fiddle

编辑

获取更多列并不难。我相信你再多花点功夫就能弄明白:

select *
from (
  select p.*,
         row_number() over (partition by p.first_name
                            order by case p.last_name
                                     when 'Smith' then 1
                                     when 'Jones' then 2
                                     when 'Gupta' then 3
                                     end desc) as rn
  from people p
  where p.last_name in ('Smith','Jones','Gupta')
)
where rn = 1;

Answer 2

或者这样:

select first_name, 
       max(last_name) 
           keep (dense_rank first order by decode(last_name,
                                                  'Smith', 1,
                                                  'Jones', 2,
                                                  'Gupta', 3) desc)
 group by first_name

Oracle "FIRST"/"LAST"函数允许从最大/最小值行的其他列中获取值(例如获取员工的最大工资的last_name，或者像在这种情况下-从最大排名的行中获取last_name)
http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions056.htm