如何使用mysql获取连续数字? [英] How to get consecutive numbers using mysql?

问题描述

我想检索连续数字及其计数，例如在我的数据库中我有这样的输入

I want to retrieve the consecutive numbers and its counts, for example in my db i have inputs like

1,2,3,4,5,9,10,12,14,15,16,51,81

我期望输出

Numbers         counts
  1-5              5
  9-10             2
 12-16             4
   51              1
   81              1

如果有人知道，请分享解决方案......

Please share the solution if anyone knows....

推荐答案

免责声明:这是为了好玩.使用您选择的实际编程语言可能更容易.

这并不像看起来那么容易，你必须使用 用户定义的变量.

This is not as easy as it first looks, you have to use user-defined variables.

示例数据:

CREATE TABLE t
    (`id` int)
;

INSERT INTO t
    (`id`)
VALUES
    (1),
    (2),
    (3),
    (4),
    (5),
    (9),
    (10),
    (12),
    (14),
    (15),
    (16),
    (51),
    (81)
;

查询:

SELECT CONCAT_WS('-', MIN(id), IF(MAX(id) = MIN(ID), NULL, MAX(id))), COUNT(*)
FROM (
SELECT 
id
, @group_number := IF(@prev != id - 1, @group_number + 1, @group_number) AS gn
, @prev := id
FROM 
t,
(SELECT @group_number := 0, @prev := NULL) var_init_subquery
ORDER BY id
) sq
GROUP BY gn

结果:

| CONCAT_WS('-', MIN(id), IF(MAX(id) = MIN(ID), NULL, MAX(id))) | COUNT(*) |
|---------------------------------------------------------------|----------|
|                                                           1-5 |        5 |
|                                                          9-10 |        2 |
|                                                            12 |        1 |
|                                                         14-16 |        3 |
|                                                            51 |        1 |
|                                                            81 |        1 |

• 在 sqlfiddle
中实时查看它的运行情况
• see it working live in an sqlfiddle

这篇关于如何使用mysql获取连续数字?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！