假设我有一张桌子

CustomerID  Country
1   Germany
6   Germany
17  Germany
25  Germany
32  USA
36  USA
39  Germany
43  USA
44  Germany


我想创建一个新列,以指示有关国家/地区的外观

CustomerID  Country  Count
1           Germany    1
6           Germany    2
17          Germany    3
25          Germany    4
32            USA      1
36            USA      2
39          Germany    5
43            USA      3
44          Germany    6


如何用SQL编写

最佳答案

在MySQL 8+中,您使用row_number()

select t.*,
       row_number() over (partition by country order by CustomerID) as counter
from t;


在早期版本中,有多种方法。一个是相关的子查询:

select t.*,
       (select count(*)
        from t t2
        where t2.country = t.country and
              t2.CustomerID <= t.CustomerID
       ) as counter
from t;

10-08 08:59