sql-server - 卡在获取交易价值的计算

Question

我有一个事件表，想根据最近的交易值进行计算。

create table evt (evnt integer not null,
value integer not null,
time datetime not null,
)

和查询：

SELECT evnt ,value
FROM evt where time IN (select max(time) from events group by evnt )
   GROUP BY evnt ,value

insert into evt values (2,5,'2018-05-09 12:42:00')
insert into evt values (4,-42,'2018-05-09 13:19:57')
insert into evt values (2,2,'2018-05-09 14:48:30')
insert into evt values (2,7,'2018-05-09 12:54:39')
insert into evt values (3,16,'2018-05-09 13:19:57')
insert into evt values (3,20,'2018-05-09 15:01:09')

输出

evnt    |   Value
-------------------
2       |  -5
3       |   4

标签： sql-server

Answer 1

根据您的简要描述，我想这就是您想要的

select  evnt, sum(case when rn = 1 then value else -value end)
from
(
    select  *, rn = row_number() over (partition by evnt order by time desc)
    from    evt
) d
where   d.rn    <= 2
group by evnt
having  count(*) = 2