求助大神 mysql 查询数据出现间隔次数 最后50分了

wap816 2020-10-14 11:13:23

求助大神

mysql> select * from table1;
+----+------+
| id | num |
+----+------+
| 1 | 12 |
| 2 | 13 |
| 3 | 33 |
| 4 | 24 |
| 5 | 43 |
| 6 | 13 |
| 7 | 56 |
| 8 | 87 |
| 9 | 27 |
| 10 | 92 |
| 11 | 24 |
| 12 | 92 |
| 13 | 13 |
+----+------+

我想实现的语句子是num最长的的间隔数，开始的id和结束的id,上面这种的结果要

13最长间隔7 开始id6 结束id13
24最长间隔7 开始id4 结束id11
12...
33...
43...
...其他的数字
请问MYSQL要怎么写
...全文
866 13 打赏 收藏 转发到动态 举报
写回复
用AI写文章
13 条回复
切换为时间正序
请发表友善的回复…
发表回复
wap816 2020-11-03
  • 打赏
  • 举报
 回复
引用 13 楼 AHUA1001 的回复:
这里table1表里的数据，您可以随意改，怎么改，结果都是对的。
比如说id不是连续的,可能中间有间隔了,按照你的方法无法获取这个id值.谢谢 +----+------+ | id | num | +----+------+ | 11 | 12 | | 21 | 13 | | 31 | 33 | | 41 | 24 | | 51 | 43 | | 61 | 13 | | 71 | 56 | | 81 | 87 | | 91 | 27 | | 101 | 92 | | 111 | 24 | | 121 | 92 | | 131 | 13 | +----+------+
AHUA1001 2020-10-30
  • 打赏
  • 举报
 回复
这里table1表里的数据，您可以随意改，怎么改，结果都是对的。
wap816 2020-10-20
  • 打赏
  • 举报
 回复
引用 11 楼 AHUA1001 的回复:
借用楼上几位的劳动成果，这样就比较完美了。 CREATE TABLE table1 (id INT,num INT); INSERT INTO table1 VALUES(1,12),(2,13),(3,33),(4,24),(5,43),(6,13),(7,56),(8,87),(9,27), (10,92),(11,24),(12,92),(13,13); SELECT T.ID,T.NUM,T.N `最长间隔开始ID`,T.N2 `最长间隔结束ID`,T.N2-T.N `最长间隔` FROM (SELECT T1.*,(SELECT MIN(T2.N) FROM ( SELECT T.ID,T.NUM,@N:=@N+1 N FROM ( SELECT T.*,@N:=0 FROM table1 T ) T ORDER BY T.ID ) T2 WHERE T2.NUM=T1.NUM AND T2.N>T1.N) N2 FROM ( SELECT T.ID,T.NUM,@N:=@N+1 N FROM ( SELECT T.*,@N:=0 FROM table1 T ) T ORDER BY T.ID ) T1 ) T WHERE T.N2 IS NOT NULL AND (NUM,T.N2-T.N) IN (SELECT NUM,MAX(N) FROM ( SELECT T.ID,T.NUM,T.N N1,T.N2,T.N2-T.N N FROM (SELECT T1.*,(SELECT MIN(T2.N) FROM ( SELECT T.ID,T.NUM,@N:=@N+1 N FROM ( SELECT T.*,@N:=0 FROM table1 T ) T ORDER BY T.ID ) T2 WHERE T2.NUM=T1.NUM AND T2.N>T1.N) N2 FROM ( SELECT T.ID,T.NUM,@N:=@N+1 N FROM ( SELECT T.*,@N:=0 FROM table1 T ) T ORDER BY T.ID ) T1 ) T WHERE T.N2 IS NOT NULL ) T GROUP BY NUM) ;
感谢大神的费心.不过发现开始id和结束id是使用的行号.实际环境中基本上不可能id和行号相同.能否再费心一下,换成真实的id,例如表中id不是1开头的呢,谢谢
AHUA1001 2020-10-15
  • 打赏
  • 举报
 回复
借用楼上几位的劳动成果，这样就比较完美了。
CREATE TABLE table1
(id INT,num INT);

INSERT INTO table1
VALUES(1,12),(2,13),(3,33),(4,24),(5,43),(6,13),(7,56),(8,87),(9,27),
(10,92),(11,24),(12,92),(13,13);

SELECT T.ID,T.NUM,T.N `最长间隔开始ID`,T.N2 `最长间隔结束ID`,T.N2-T.N `最长间隔` FROM (SELECT T1.*,(SELECT MIN(T2.N) FROM (
SELECT T.ID,T.NUM,@N:=@N+1 N FROM (
SELECT T.*,@N:=0 FROM table1 T ) T ORDER BY T.ID ) T2 WHERE T2.NUM=T1.NUM AND T2.N>T1.N) N2 FROM (
SELECT T.ID,T.NUM,@N:=@N+1 N FROM (
SELECT T.*,@N:=0 FROM table1 T ) T ORDER BY T.ID ) T1 ) T WHERE T.N2 IS NOT NULL AND (NUM,T.N2-T.N) IN (SELECT NUM,MAX(N) FROM (
SELECT T.ID,T.NUM,T.N N1,T.N2,T.N2-T.N N FROM (SELECT T1.*,(SELECT MIN(T2.N) FROM (
SELECT T.ID,T.NUM,@N:=@N+1 N FROM (
SELECT T.*,@N:=0 FROM table1 T ) T ORDER BY T.ID ) T2 WHERE T2.NUM=T1.NUM AND T2.N>T1.N) N2 FROM (
SELECT T.ID,T.NUM,@N:=@N+1 N FROM (
SELECT T.*,@N:=0 FROM table1 T ) T ORDER BY T.ID ) T1 ) T WHERE T.N2 IS NOT NULL ) T GROUP BY NUM) ;
AHUA1001 2020-10-15
  • 打赏
  • 举报
 回复
直接执行这里的语句，把这里的UNION ALL相关内容，替换成你的表即可。
SELECT T.ID,T.NUM,T.N `最长间隔开始ID`,T.N2 `最长间隔结束ID`,T.N2-T.N `最长间隔` FROM (SELECT T1.*,(SELECT MIN(T2.N) FROM (
SELECT T.ID,T.NUM,@N:=@N+1 N FROM (
SELECT T.*,@N:=0 FROM ( SELECT 1 ID,12 NUM FROM DUAL UNION ALL
SELECT 2 ID,13 NUM FROM DUAL UNION ALL
SELECT 3 ID,33 NUM FROM DUAL UNION ALL
SELECT 4 ID,24 NUM FROM DUAL UNION ALL
SELECT 5 ID,43 NUM FROM DUAL UNION ALL
SELECT 6 ID,13 NUM FROM DUAL UNION ALL
SELECT 7 ID,56 NUM FROM DUAL UNION ALL
SELECT 8 ID,87 NUM FROM DUAL UNION ALL
SELECT 9 ID,27 NUM FROM DUAL UNION ALL
SELECT 10 ID,92 NUM FROM DUAL UNION ALL
SELECT 11 ID,24 NUM FROM DUAL UNION ALL
SELECT 12 ID,92 NUM FROM DUAL UNION ALL
SELECT 13 ID,13 NUM FROM DUAL ) T ) T ORDER BY T.ID ) T2 WHERE T2.NUM=T1.NUM AND T2.N>T1.N) N2 FROM (
SELECT T.ID,T.NUM,@N:=@N+1 N FROM (
SELECT T.*,@N:=0 FROM ( SELECT 1 ID,12 NUM FROM DUAL UNION ALL
SELECT 2 ID,13 NUM FROM DUAL UNION ALL
SELECT 3 ID,33 NUM FROM DUAL UNION ALL
SELECT 4 ID,24 NUM FROM DUAL UNION ALL
SELECT 5 ID,43 NUM FROM DUAL UNION ALL
SELECT 6 ID,13 NUM FROM DUAL UNION ALL
SELECT 7 ID,56 NUM FROM DUAL UNION ALL
SELECT 8 ID,87 NUM FROM DUAL UNION ALL
SELECT 9 ID,27 NUM FROM DUAL UNION ALL
SELECT 10 ID,92 NUM FROM DUAL UNION ALL
SELECT 11 ID,24 NUM FROM DUAL UNION ALL
SELECT 12 ID,92 NUM FROM DUAL UNION ALL
SELECT 13 ID,13 NUM FROM DUAL ) T ) T ORDER BY T.ID ) T1 ) T WHERE T.N2 IS NOT NULL AND (NUM,T.N2-T.N) IN (SELECT NUM,MAX(N) FROM (
SELECT T.ID,T.NUM,T.N N1,T.N2,T.N2-T.N N FROM (SELECT T1.*,(SELECT MIN(T2.N) FROM (
SELECT T.ID,T.NUM,@N:=@N+1 N FROM (
SELECT T.*,@N:=0 FROM ( SELECT 1 ID,12 NUM FROM DUAL UNION ALL
SELECT 2 ID,13 NUM FROM DUAL UNION ALL
SELECT 3 ID,33 NUM FROM DUAL UNION ALL
SELECT 4 ID,24 NUM FROM DUAL UNION ALL
SELECT 5 ID,43 NUM FROM DUAL UNION ALL
SELECT 6 ID,13 NUM FROM DUAL UNION ALL
SELECT 7 ID,56 NUM FROM DUAL UNION ALL
SELECT 8 ID,87 NUM FROM DUAL UNION ALL
SELECT 9 ID,27 NUM FROM DUAL UNION ALL
SELECT 10 ID,92 NUM FROM DUAL UNION ALL
SELECT 11 ID,24 NUM FROM DUAL UNION ALL
SELECT 12 ID,92 NUM FROM DUAL UNION ALL
SELECT 13 ID,13 NUM FROM DUAL ) T ) T ORDER BY T.ID ) T2 WHERE T2.NUM=T1.NUM AND T2.N>T1.N) N2 FROM (
SELECT T.ID,T.NUM,@N:=@N+1 N FROM (
SELECT T.*,@N:=0 FROM ( SELECT 1 ID,12 NUM FROM DUAL UNION ALL
SELECT 2 ID,13 NUM FROM DUAL UNION ALL
SELECT 3 ID,33 NUM FROM DUAL UNION ALL
SELECT 4 ID,24 NUM FROM DUAL UNION ALL
SELECT 5 ID,43 NUM FROM DUAL UNION ALL
SELECT 6 ID,13 NUM FROM DUAL UNION ALL
SELECT 7 ID,56 NUM FROM DUAL UNION ALL
SELECT 8 ID,87 NUM FROM DUAL UNION ALL
SELECT 9 ID,27 NUM FROM DUAL UNION ALL
SELECT 10 ID,92 NUM FROM DUAL UNION ALL
SELECT 11 ID,24 NUM FROM DUAL UNION ALL
SELECT 12 ID,92 NUM FROM DUAL UNION ALL
SELECT 13 ID,13 NUM FROM DUAL ) T ) T ORDER BY T.ID ) T1 ) T WHERE T.N2 IS NOT NULL ) T GROUP BY NUM)
evanweng 2020-10-15
  • 打赏
  • 举报
 回复
引用 8 楼 银点 的回复:
[quote=引用 7 楼 evanweng 的回复:][quote=引用 5 楼 evanweng 的回复:]select t3.* from ( select t1.num, min(t2.id) - t1.id len, t1.id start_id, min(t2.id) end_id from table1 t1 left join table1 t2 on t1.num = t2.num and t2.id > t1.id where t2.id is not null group by t1.id, t1.num order by t1.num asc, len desc ) t3 group by t3.num 不过有个问题，高版本select的值要在group by里面。
select t1.num, min(t2.id) - t1.id len, t1.id start_id, min(t2.id) end_id from table1 t1 left join table1 t2 on t1.num = t2.num and t2.id > t1.id where t2.id is not null group by t1.id, t1.num order by t1.num asc, len desc 或者这样子查询出来后，在代码中通过num分组，然后取第一条数据[/quote] id是连续的，您这是一个很好的办法。如果id不连续呢？[/quote] id不连续就先加一个行号
wap816 2020-10-14
  • 打赏
  • 举报
 回复
引用 1 楼 银点 的回复:
没看明白你的需求，什么是最长间隔？
例如 数字13 距离第二个13是间隔了4行 第二个13距离第三个13间隔了7行,取最长间隔7行这个数据,开始id6 结束id13
银点 2020-10-14
  • 打赏
  • 举报
 回复
没看明白你的需求，什么是最长间隔？
银点 2020-10-14
  • 打赏
  • 举报
 回复
引用 7 楼 evanweng 的回复:
[quote=引用 5 楼 evanweng 的回复:]select t3.* from ( select t1.num, min(t2.id) - t1.id len, t1.id start_id, min(t2.id) end_id from table1 t1 left join table1 t2 on t1.num = t2.num and t2.id > t1.id where t2.id is not null group by t1.id, t1.num order by t1.num asc, len desc ) t3 group by t3.num 不过有个问题，高版本select的值要在group by里面。
select t1.num, min(t2.id) - t1.id len, t1.id start_id, min(t2.id) end_id from table1 t1 left join table1 t2 on t1.num = t2.num and t2.id > t1.id where t2.id is not null group by t1.id, t1.num order by t1.num asc, len desc 或者这样子查询出来后，在代码中通过num分组，然后取第一条数据[/quote] id是连续的，您这是一个很好的办法。如果id不连续呢？
evanweng 2020-10-14
  • 打赏
  • 举报
 回复
引用 5 楼 evanweng 的回复:
select t3.* from ( select t1.num, min(t2.id) - t1.id len, t1.id start_id, min(t2.id) end_id from table1 t1 left join table1 t2 on t1.num = t2.num and t2.id > t1.id where t2.id is not null group by t1.id, t1.num order by t1.num asc, len desc ) t3 group by t3.num 不过有个问题，高版本select的值要在group by里面。
select t1.num, min(t2.id) - t1.id len, t1.id start_id, min(t2.id) end_id from table1 t1 left join table1 t2 on t1.num = t2.num and t2.id > t1.id where t2.id is not null group by t1.id, t1.num order by t1.num asc, len desc 或者这样子查询出来后，在代码中通过num分组，然后取第一条数据
evanweng 2020-10-14
  • 打赏
  • 举报
 回复
select t3.* from ( select t1.num, min(t2.id) - t1.id len, t1.id start_id, min(t2.id) end_id from table1 t1 left join table1 t2 on t1.num = t2.num and t2.id > t1.id where t2.id is not null group by t1.id, t1.num order by t1.num asc, len desc ) t3 group by t3.num 不过有个问题，高版本select的值要在group by里面。
chengangcsdn 2020-10-14
  • 打赏
  • 举报
 回复 
create table table1
(id int,num int);

insert into table1
values(1,12),(2,13),(3,33),(4,24),(5,43),(6,13),(7,56),(8,87),(9,27),
(10,92),(11,24),(12,92),(13,13);

select num,group_concat(id order by id) as idstr from table1 group by num
---能解决一半吧。你业务端再取idstr 串解析一下 取串之间的最大差就可以了。
老紫竹 2020-10-14
  • 打赏
  • 举报
 回复
select num,max(id)-min(id)+1 from T group by num 你难道要的是这个 最大-最小+1 吗？
mysql数据库主从同步配置
mysql 主从配置 限制同步数据库 一、概述MySQL从3.23.15版本以后提供数据库复制(replication)功能，利用该功能可以实现两个数据库同步、主从模式、互相备份模式的功能。本文档主要阐述了如何在linux系统中利用mysql的replication进行双机热备的配置。二、环境操作系统：Linux 2.6...
已解决-求助bootstrap-table x-editable 修改值之后如何通过ajax用PHP传到数据库
目的：通过bootstrap table x-editable 实现数据可以页面直接更新，数据直接从后台拉出显示， 问题出在如何把更新传递到后台。 求助大神如何写ajax来把值传到后台， PHP插入数据库后回显到页面。 架构 前端HTML代码： <!DOCTYPE html> <html> <head> <meta charset="utf-8"> <link rel="icon" href="img/favicon.ico"
python消费datahub_datahub消费数据
12月17日更新 请问下同时消费多个topic的情况下，在richmap里面可以获取到当前消息所属的topic吗？各位大佬，你们实时都是怎样重跑数据的?有木有大神知道Flink能否消费多个kafka集群的数据？这个问题有人遇到吗?你们实时读取广业务库到kafka是通过什么读的？kafka connector 的原理是定时去轮询，这样如果表多了，会不会影响业务库的性能？甚至把业务库搞挂？有没有fli...
mysql语句分段关键字_mysql按时间分段统计的sql语句怎么写好？
我的数据库表结构如下：字段分别是ID，NUM1,NUM2, TIME1，TIME2, MONEY.想要按照15分钟为间隔来统计ID的数量。效果如同下面的sql语句。SELECTsum(CASE when TIME1 >= '2014-02-27 8:00:00' and TIME1 < '2014-02-27 8:15:00' then 1 else 0 end) AS '1',s...
有哪些值得推荐的数据可视化工具？
作者：文兄 链接：https://www.zhihu.com/question/19929609/answer/133825589 来源：知乎 著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。 谢邀。本答案提要：1.plotly 2.R ggplot23.无需编程语言的工具（7个）4.基于JavaScript实现的工具（8个）5.基于其他语言的工具（5个）6.地图数据可视化工具（7个）7.金融（股票）数据可视化工具（2个）8.时间轴数据可视化工具（2个）9.函数与公式数据可视化工具（2个
MySQL

56,677

社区成员

56,708

社区内容

发帖
与我相关
我的任务
社区描述
MySQL相关内容讨论专区
社区管理员
  • MySQL
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章