sql - 如何在Oracle中仅选择值变化？

Question

我有一个表格，其中包含根据周期的值

我只想选择更改

示例:

表格列:

Member
Status
Period

表格数据:

Member  Status  Period----------------------     A       X       1     A       X       2     A       Y       3     A       Y       4     B       X       1     B       X       2     B       X       3     B       Z       4

I would like the query to return something like this

   Member A Status change X to Y Period 2 to 3
   Member B Status change X to Z Period 3 to 4

我遇到问题，因为 Oracle 不允许在子查询中排序

有什么查询的想法吗？

Answer 1

您可以使用窗口函数来实现:

select *
from (
  select member,
         status, 
         period,
         lag(status) over (partition by member order by period) as prev_status, 
         lag(period) over (partition by member order by period) as prev_period
  from the_table
) 
where status <> prev_status