问题: table1(id:自增id,money:费用)问题:按id顺序累加money,取出累计值与1000相差最小差值的id。
建表语句:
create table `table1` (
`id` int default null auto_increment primary key,
`money` int default null
) engine=innodb default charset=utf8;
insert into `table1` values (1,100),(1,300),(1,500),(1,700),(2,200),(2,400),(2,500),(2,700),(2,900),(3,100),(3,400),(3,700);
![36095748ec3f52f195e9ae48eef73da4.png](https://i-blog.csdnimg.cn/blog_migrate/5d03f644e35c220f3bb35887a79a340e.png)
内容简单,本来不值当记一下的,但是表都建好了,又不想白写,所以就记录一下!
SQL语句:
select id, abs(sum(money) - 1000) as diff
from table1
group by id
order by diff
limit 1
运行结果:
![7e3dbb7df3ddf20ee1e0daa441efba1f.png](https://i-blog.csdnimg.cn/blog_migrate/08b7bf39b4452d37daffda3039a511ae.png)
题目来自逆流大神的专栏:
逆流:sql 面试题(难题汇总)zhuanlan.zhihu.com![54ffea4e837161c2c80ad67191b1b89c.png](https://i-blog.csdnimg.cn/blog_migrate/a6c1b9bac21eedae0ae36ddb9599d2b2.jpeg)