你也可以用 subquery :

update table t
     set prio_no = (select count(*) from table t1 where t1.date <= t.date);

您可以简单地执行以下操作：

set @rn := 0

update t
    set prio_no = (@rn := @rn + 1)
    order by date;

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(date DATE NOT NULL UNIQUE);

INSERT INTO my_table VALUES
('2018-06-13'),
('2018-06-12'),
('2018-06-14'),
('2018-06-15'),
('2018-06-16'),
('2018-06-11');

SELECT @i:=@i+1 id,date FROM my_table, (SELECT @i:=0) vars ORDER BY date;
+------+------------+
| id   | date       |
+------+------------+
|    1 | 2018-06-11 |
|    2 | 2018-06-12 |
|    3 | 2018-06-13 |
|    4 | 2018-06-14 |
|    5 | 2018-06-15 |
|    6 | 2018-06-16 |
+------+------------+

ALTER TABLE my_table ADD COLUMN id INT NULL;

UPDATE my_table x JOIN
(
SELECT @i:=@i+1 id,date FROM my_table, (SELECT @i:=0) vars ORDER BY date
) y
ON y.date = x.date
SET x.id = y.id;

ALTER TABLE my_table MODIFY COLUMN id INT NOT NULL AUTO_INCREMENT PRIMARY KEY;

SELECT * FROM my_table;
+------------+----+
| date       | id |
+------------+----+
| 2018-06-13 |  3 |
| 2018-06-12 |  2 |
| 2018-06-14 |  4 |
| 2018-06-15 |  5 |
| 2018-06-16 |  6 |
| 2018-06-11 |  1 |
+------------+----+