有这样的表
+--------+------------+
| orderID | productID |
+--------+------------+
| 100 | 10 |
| 100 | 11 |
| 101 | 10 |
| 101 | 11 |
| 101 | 12 |
+--------+------------+
我需要添加第三列,它是产品当前位置的计数器。类似:
+--------+------------+--------------+
| orderID | productID | currentIndex |
+--------+------------+--------------+
| 100 | 10 | 1 |
| 100 | 11 | 2 |
| 101 | 10 | 1 |
| 101 | 11 | 2 |
| 101 | 12 | 3 |
+--------+------------+--------------+
能帮帮我吗?
我现在有这个问题:
SELECT orderID, productID
FROM orders;
如果您运行的是MySQL 8.0,`row_number((完全符合您的要求:
select orderid, productid,
row_number() over(partition by orderid order by productid) currentindex
from orders;
在早期版本中,备选方案要么是用户变量,要么是相关的子查询。我将推荐第二种选择——用户变量很难处理,现在正式计划在未来弃用:
select orderid, productid,
(select count(*) from orders o1 where o1.orderid = o.orderid and o1.productid <= o.productid) currentindex
from orders o;