如果id已被选中，MySQL选择增量计数器

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

1. 您可以使用子查询来获取具有与当前地址相同的 customer_id的地址数，但id小于当前地址的。 假设您的表(或视图)名为address_table。然后： 选择a.id，a.customer_id，a.name，a.address，(从address_table选择计数(*)，其中customer_id=a.customer_id和id
2. 另一个较难理解但仍然有效的解决方案是使用JOIN+GROUPBY： 看看草莓的答案。

Answer 2

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的答案)。

Answer 3

没有测试，但是下面有两种方法。

首先，使用序列号来计数数值。

SELECT id, customer_id, address, CONCAT(name, "'s address #", addr_seq)
FROM 
(
    SELECT id, customer_id, name, address, @counter:=IF(@prev_customer_id = customer_id, @counter + 1, 1) AS addr_seq, @prev_customer_id := customer_id
    FROM address_table
    CROSS JOIN (SELECT @counter:= 0, @prev_customer_id:=0) Sub1
    ORDER BY customer_id, id
) Sub2
ORDER BY id

或使用联接和计数

SELECT id, customer_id, address, CONCAT(name, "'s address #", addr_seq)
FROM 
(
    SELECT a.id,a. customer_id, a.address, COUNT(b.id) AS addr_seq
    FROM address_table a
    INNER JOIN address_table b
    WHERE a.customer_id = b.customer_id
    AND a.id >= b.id
    GROUP BY a.id,a. customer_id, a.address
) Sub1
ORDER BY id