mysql - 如何找到下一个订单ID mysql

Question

示例输入

order_id Customer_id Order_date    
1          1         2017-11-01    
2          2         2017-11-02    
3          1         2017-11-03    
4          2         2017-11-04

期望的输出

order_id next_order_id

1              3    
2              4    
3    
4

Answer 1

你可以使用 LEAD :

SELECT order_id,
 LEAD(order_id) OVER(PARTITION BY Customer_id ORDER BY Order_date) AS next_order
FROM tab
ORDER BY order_id;

DBFiddle Demo - MySQL 8.0

---

对于以前的 MySQL 版本:

SELECT order_id,
     (SELECT order_id 
     FROM tab t2 
     WHERE t1.customer_id = t2.customer_id 
       and t2.Order_date > t1.order_date 
     ORDER BY Order_date LIMIT 1 ) AS next_order
FROM tab t1
ORDER BY order_id;

DBFiddle Demo2