mysql - 如何在 MySQL 中获取行出现值？

Question

我有下表

id   name   amount
1    aaa     1000
2    bbb     1500
3    ccc     1700
4    ddd     2000
5    aaa     1400
6    aaa     1700
7    bbb     1800

我需要的是多一列，根据名字显示出现值如下

 id   name   amount   occurrence
  1    aaa     1000     1
  2    bbb     1500     1
  3    ccc     1700     1
  4    ddd     2000     1
  5    aaa     1400     2
  6    aaa     1700     3
  7    bbb     1800     2

Answer 1

您可以使用相关子查询来完成:

SELECT id, name, amount, 
       (SELECT COUNT(*)
        FROM mytable AS t2
        WHERE t2.name = t1.name AND t2.id <= t1.id) AS occurrence
FROM mytable AS t1   

Demo here