欄位名: time 和 stype
表名:test
按照time時間段篩選出stype最大間隔,這個陳述句怎么寫?
搞了一天了也沒結果.比如圖中,是 time>=2018-05-23 13:21:01 and time<=2018-05-23 14:30:43 來篩選的
然后篩選出這個時間段,再篩選出 stype=1 的最大出現間隔,
uj5u.com熱心網友回復：
寫個存盤程序來處理的話，很容易。。uj5u.com熱心網友回復：
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>
uj5u.com熱心網友回復：
你那代碼執行效率太低了uj5u.com熱心網友回復：
如果某一個stype，只出現了一次，并且是最后一條資料，應該算什么。uj5u.com熱心網友回復：
只能 用表關聯值相等,然后得出時間值,相減了.轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/88011.html
標籤：MySQL
上一篇：通用型欄位圖片顯示問題