如果已选择 id，则 MySQL SELECT 递增计数器

Question

我有一个非常简单的 MySQL 表，其结构如下:

id   | customer_id |  name  | address
-------------------------------
1    |      1      |  John  | 123 street
2    |      2      |  Jane  | 456 lane
3    |      1      |  John  | 789 square
4    |      1      |  John  | 000 street
5    |      3      |  Paul  | 666 hell

每个客户可以有多个地址，我想选择所有地址，每个 ID 都有一个递增的计数器，例如:

id   | customer_id |  address   |    count_by_id
----------------------------------------------------
1    |      1      | 123 street |  John's address #1
2    |      2      | 456 lane   |  Jane's address #1
3    |      1      | 789 square |  John's address #2
4    |      1      | 000 street |  John's address #3
5    |      3      | 666 hell   |  Paul's address #1

我该如何继续？

Answer 1

SELECT x.*
     , COUNT(*) rank 
  FROM customer_address x 
  JOIN customer_address y 
    ON y.customer_id = x.customer_id 
   AND y.customer_address_id <= x.customer_address_id 
 GROUP 
    BY x.customer_id
     , x.customer_address_id 
 ORDER 
    BY customer_address_id;

如果性能是个问题，我们可以讨论(或者看看@kickstart 的回答)