求支付表中按id累积和最接近100的那条记录

此例源自美团的一道SQL面试题

支付表结构：

create table hy_payment(
    id number(4,0) primary key,
    pay number(3,0) not null)

可以这样给它充值：

insert into hy_payment(id,pay) values('1','110');
insert into hy_payment(id,pay) values('2','9');
insert into hy_payment(id,pay) values('3','5');
insert into hy_payment(id,pay) values('4','23');
insert into hy_payment(id,pay) values('5','78');
insert into hy_payment(id,pay) values('6','22');
insert into hy_payment(id,pay) values('7','31');
insert into hy_payment(id,pay) values('8','8');
insert into hy_payment(id,pay) values('9','4');
insert into hy_payment(id,pay) values('11','6');
insert into hy_payment(id,pay) values('12','5');

首先把从id=1到当前id的总支付值和与100的偏差值找出来：

select id,pay,sum(pay) over (order by id) as sumpay,abs(sum(pay) over (order by id)-100) as bias from hy_payment

从上表我们已经可以125是最接近100的值了，然后把它排序一下：

select a.*,rank() over(order by a.bias) as seq from (select id,pay,sum(pay) over (order by id) as sumpay,abs(sum(pay) over (order by id)-100) as bias from hy_payment) a order by a.bias

最后我们只要第一条，即seq=1的那条：

select b.*
from
(select a.*,rank() over(order by a.bias) as seq from (select id,pay,sum(pay) over (order by id) as sumpay,abs(sum(pay) over (order by id)-100) as bias from hy_payment) a order by a.bias) b
where b.seq=1

从这条记录可以看出，id(1~5)累计值125是最接近100的记录。

 

以上用到的所有SQL：

create table hy_payment(
    id number(4,0) primary key,
    pay number(3,0) not null)
    
insert into hy_payment(id,pay) values('1','110');
insert into hy_payment(id,pay) values('2','9');
insert into hy_payment(id,pay) values('3','5');
insert into hy_payment(id,pay) values('4','23');
insert into hy_payment(id,pay) values('5','78');
insert into hy_payment(id,pay) values('6','22');
insert into hy_payment(id,pay) values('7','31');
insert into hy_payment(id,pay) values('8','8');
insert into hy_payment(id,pay) values('9','4');
insert into hy_payment(id,pay) values('11','6');
insert into hy_payment(id,pay) values('12','5');

commit;

select * from hy_payment

select id,pay,sum(pay) over (order by id) as sumpay,abs(sum(pay) over (order by id)-100) as bias from hy_payment


select a.*,rank() over(order by a.bias) as seq from (select id,pay,sum(pay) over (order by id) as sumpay,abs(sum(pay) over (order by id)-100) as bias from hy_payment) a order by a.bias

select b.*
from
(select a.*,rank() over(order by a.bias) as seq from (select id,pay,sum(pay) over (order by id) as sumpay,abs(sum(pay) over (order by id)-100) as bias from hy_payment) a order by a.bias) b
where b.seq=1

--2020-04-01--