HQL面试题练习 —— 取出累计值与1000差值最小的记录
题目来源：滴滴
目录
- 1 题目
- 2 建表语句
- 3 题解
1 题目
已知有表 t_cost_detail 包含 id 和 money 两列，id 为自增，请累加计算money 值，并求出累加值与 1000 差值最小的记录。
+-----+--------+
| id | money |
+-----+--------+
| 1 | 200 |
| 2 | 300 |
| 3 | 200 |
| 4 | 100 |
| 5 | 150 |
| 6 | 80 |
| 7 | 100 |
| 8 | 200 |
+-----+--------+
样例结果
+-----+--------+
| id | money |
+-----+--------+
| 6 | 80 |
+-----+--------+
2 建表语句
CREATE TABLE IF NOT EXISTS t_cost_detail (id bigint, --idmoney bigint -- money
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS ORC;insert into t_cost_detail(id, money) values
(1,200),
(2,300),
(3,200),
(4,100),
(5,150),
(6,80),
(7,100),
(8,200);
3 题解
selectid,money
from(selectid,money,row_number()over(order by abs_diff) as rnfrom(selectid,money,sum(money) over(order by id) as sum_money,abs(sum(money) over(order by id)-1000) as abs_difffrom t_cost_detail) t) tt
where rn = 1;