本文主要包括：
排序：判断下一个值如果和上一个不一样，new_id就加1
--- 表结构
mysql> desc test;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| age | int | YES | | NULL | |
+-------+------+------+-----+---------+-------+
---- 数据
mysql> select * from test;
+------+------+
| id | age |
+------+------+
| 1 | 10 |
| 2 | 10 |
| 3 | 10 |
| 4 | 10 |
| 5 | 11 |
| 6 | 11 |
| 7 | 10 |
| 8 | 10 |
+------+------+
--------------- sql
SELECT
SUM(change_new_id) OVER (ORDER BY id) AS new_id,
age
FROM
(
SELECT
id,
age,
CASE WHEN age <> LAG(age) OVER (ORDER BY id) THEN 1 ELSE 0 END AS change_new_id
FROM
test
) AS change_table;
-- 执行结果
+--------+------+
| new_id | age |
+--------+------+
| 0 | 10 |
| 0 | 10 |
| 0 | 10 |
| 0 | 10 |
| 1 | 11 |
| 1 | 11 |
| 2 | 10 |
| 2 | 10 |
+--------+------+