如何使用mysql获取连续数字? [英] How to get consecutive numbers using mysql?
本文介绍了如何使用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屋!
查看全文