mysql - 如何在考虑记录序列的情况下对列进行 GROUP BY？

Question

抱歉我的英语不好。

我正在尝试对一堆 user_id 进行 GROUP BY 并考虑其序列。我会尽力向您解释我到底想要什么:

表:订单

+-----------+----------+------------+
|    id     | user_id  |   amount   |
+-----------+----------+------------+
|   1       | 100      |     5      |
|   2       | 100      |     5      |
|   3       | 100      |     10     |
|   4       | 101      |     15     |
|   5       | 101      |     10     |
|   6       | 101      |     5      |
|   7       | 102      |     5      |
|   8       | 100      |     5      |
|   9       | 100      |     10     |
|   10      | 102      |     10     |
+-----------+----------+------------+

我想要的是:

+-----------+----------+------------+----------------+
| user_id   |   count  |  total_amount  |  id_range  |
+-----------+----------+------------+----------------+
|   100     |    3     |       20       |     1,3    |
|   101     |    3     |       30       |     4,6    |
|   102     |    1     |       5        |     7,7    |
|   100     |    2     |       15       |     8,9    |
|   102     |    1     |       10       |    10,10   |
+-----------+----------+------------+----------------+

到目前为止我尝试了什么:

SELECT user_id, count(user_id) as count, sum(amount) as total_amount FROM `orders` GROUP By user_id

但它只是对所有记录进行 GROUP BY，而不考虑其顺序。

Answer 1

MySQL 没有排名或行号函数，这使得答案有点复杂。要使用排名函数，请参阅此问题和答案:

http://stackoverflow.com/questions/1895110/row-number-in-mysql

一个可能的查询是这样的:

SELECT
  user_id,
  count(*) as cnt,
  sum(amount) as total_amount,
  CONCAT_WS(',', min(id), max(id)) AS id_range
FROM (
SELECT
  id,
  id - 
  CASE
    WHEN @prev_value = user_id THEN @rank_count := @rank_count + 1
    WHEN @prev_value := user_id THEN @rank_count
  END AS grp,
  user_id,
  amount
FROM
  orders CROSS JOIN (SELECT @prev_value:=NULL, @rank_count:=0) r
ORDER BY
  id
) s
GROUP BY grp

请看这个fiddle .