Mysql取相同数据最大的间隔数

_小生有礼了_ 2018-05-25 01:10:52

字段名: time 和 stype
表名:test
按照time时间段筛选出stype最大间隔,这个语句怎么写?

搞了一天了也没结果.

比如图中,是 time>=2018-05-23 13:21:01 and time<=2018-05-23 14:30:43 来筛选的
然后筛选出这个时间段,再筛选出 stype=1 的最大出现间隔,

...全文

1271 5 打赏收藏转发到动态举报

写回复

用AI写文章

5 条回复

切换为时间正序

请发表友善的回复…

发表回复

_小生有礼了_ 2018-05-25

打赏
举报

你那代码执行效率太低了

ACMAIN_CHM 2018-05-25

打赏
举报

mysql> select * from test;
+---------------------+-------+
| time                | stype |
+---------------------+-------+
| 2018-05-23 13:21:01 |     2 |
| 2018-05-24 13:22:01 |     5 |
| 2018-05-25 13:23:01 |     3 |
| 2018-05-26 13:24:01 |     3 |
| 2018-05-27 13:25:01 |     1 |
| 2018-05-28 13:26:01 |     4 |
| 2018-05-29 13:27:01 |     3 |
| 2018-05-30 13:28:01 |     1 |
| 2018-05-31 13:29:01 |     1 |
| 2018-06-01 13:30:01 |     2 |
| 2018-06-02 13:31:01 |     5 |
| 2018-06-03 13:32:01 |     5 |
| 2018-06-04 13:33:01 |     2 |
| 2018-06-05 13:34:01 |     3 |
| 2018-06-06 13:35:01 |     4 |
| 2018-06-07 13:36:01 |     2 |
| 2018-06-08 13:37:01 |     2 |
| 2018-06-09 13:38:01 |     1 |
| 2018-06-10 13:39:01 |     5 |
| 2018-06-11 13:40:01 |     3 |
| 2018-06-12 13:41:01 |     3 |
| 2018-06-13 13:42:01 |     3 |
| 2018-06-14 13:43:01 |     1 |
| 2018-06-15 13:44:01 |     4 |
| 2018-06-16 13:45:01 |     2 |
| 2018-06-17 13:46:01 |     3 |
| 2018-06-18 13:47:01 |     2 |
| 2018-06-19 13:48:01 |     3 |
| 2018-06-20 13:49:01 |     4 |
| 2018-06-21 13:50:01 |     1 |
| 2018-06-22 13:51:01 |     3 |
| 2018-06-23 13:52:01 |     2 |
+---------------------+-------+
32 rows in set (0.00 sec)

mysql> select max(i)
    -> from (
    -> select time,stype,
    -> (select count(*) from test where time<=a.time) as cn,
    -> (select count(*) from test where time<=a.time)-@pcn as i,
    -> @pcn:=(select count(*) from test where time<=a.time)
    -> from test a
    -> where  time>='2016-05-23 13:21:01' and time<='2019-05-23 14:30:43'
    -> and stype=1
    -> ) t;
+--------+
| max(i) |
+--------+
|      9 |
+--------+
1 row in set (0.01 sec)

mysql>