基于单列返回不同记录(Oracle)时出现问题

Question

如果我有"members"表（如下所示），我将如何获取membership_id第一次出现的记录（Oracle）。

预期成果

123                 John                      Doe                      A       P
313                 Michael                   Casey                    A       A
113                 Luke                      Skywalker                A       P

表 - 成员

membership_id       first_name                last_name                status  type
123                 John                      Doe                      A       P
313                 Michael                   Casey                    A       A
113                 Luke                      Skywalker                A       P
123                 Bob                       Dole                     A       A
313                 Lucas                     Smith                    A       A

Answer 1

SELECT membership_id,
       first_name,
       last_name,
       status,
       type
  FROM( SELECT membership_id,
               first_name,
               last_name,
               status,
               type,
               rank() over (partition by membership_id
                                order by type desc) rnk
          FROM members )
 WHERE rnk = 1

将适用于您的示例数据集。 如果可以有领带（即具有相同membership_id和相同最大type的多行），则此查询将返回所有这些行。 如果只想返回存在领带的行之一，则需要向order by添加其他条件以确保所有领带都断开，或者需要使用 row_number 函数而不是任意断开领带的rank函数。

Answer 2

Select A.*
FROM Members AS A inner join
     (Select membership_id, first(first_name) AS FN, first(last_name) AS LN 
      From Members
      Group by membership_id) AS B
     ON A.membership_id=B.membership_id and A.first_name=B.FN and A.last_name=B.LN

希望对您有所帮助！

Answer 3

select *
from members 
where rowid in (
    select min(rowid)
    from members
    group by membership_id
)