sql - 上次值从负数变为正数

Question

当 Balance 字段最后一次从负值变为正值时，如何通过 PersonID 返回日期？

在下面的示例数据中，对于 PersonID 1，它发生在 2019 年 7 月 8 日，对于 PersonID 2，它发生在 2019 年 8 月 8 日。值可以多次从负变为正，但它应该只引用它发生的最近时间。

预期输出：

我有以下示例数据

Create Table #temp
(
    PersonID int,
    ActionDate date,
    Balance money
)
insert into #temp
(
    PersonID,
    ActionDate,
    Balance
)
select
    1,
    '01 Jul 2019',
    -100
union all
select
    1,
    '02 Jul 2019',
    -45
union all
select
    1,
    '03 Jul 2019',
    -80
union all
select
    1,
    '04 Jul 2019',
    -20
union all
select
    1,
    '05 Jul 2019',
    40
union all
select
    1,
    '06 Jul 2019',
    -40
union all
select
    1,
    '07 Jul 2019',
    -90
union all
select
    1,
    '08 Jul 2019',
    -150
union all
select
    1,
    '09 Jul 2019',
    100
union all
select
    1,
    '10 Jul 2019',
    120
union all
select
    1,
    '11 Jul 2019',
    130
union all
select
    1,
    '12 Jul 2019',
    140
--
union all
select
    2,
    '01 Aug 2019',
    -100
union all
select
    2,
    '02 Aug 2019',
    -45
union all
select
    2,
    '03 Aug 2019',
    80
union all
select
    2,
    '04 Aug 2019',
    20
union all
select
    2,
    '05 Aug 2019',
    -40
union all
select
    2,
    '06 Aug 2019',
    -40
union all
select
    2,
    '07 Aug 2019',
    40
union all
select
    2,
    '08 Aug 2019',
    -40
union all
select
    2,
    '09 Aug 2019',
    45
union all
select
    2,
    '10 Aug 2019',
    65
union all
select
    2,
    '11 Aug 2019',
    23
union all
select
    2,
    '12 Aug 2019',
    105

标签： sqlsql-servertsql

Answer 1

这可能会做你想做的事not exists。它找到最后一个负余额：

select t.*
from #temp t
where t.balance < 0 and
      not exists (select 1
                  from #temp t2
                  where t2.personid = t.personid and
                        t2.actiondate > t.actiondate and
                        t2.balance < 0
                 );

如果您想要最后一次更改，您可以使用窗口函数进行过滤：

select t.*
from (select t.*,
             row_number() over (partition by personid order by actiondate desc) as seqnum
      from (select t.*,
                   lead(balance) over (partition by personid order by actiondate) as next_balance
            from #temp t
           ) t
      where t.balance < 0 and
            t.next_balance > 0
     ) t
where seqnum = 1;