求助大神
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要怎么寫
uj5u.com熱心網友回復：
沒看明白你的需求，什么是最長間隔？uj5u.com熱心網友回復：
例如 數字13 距離第二個13是間隔了4行 第二個13距離第三個13間隔了7行,取最長間隔7行這個資料,開始id6 結束id13
uj5u.com熱心網友回復：
select num,max(id)-min(id)+1 from T group by num你難道要的是這個 最大-最小+1 嗎？
uj5u.com熱心網友回復：
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 串決議一下 取串之間的最大差就可以了。
uj5u.com熱心網友回復：
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里面。
uj5u.com熱心網友回復：
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分組，然后取第一條資料
uj5u.com熱心網友回復：
id是連續的，您這是一個很好的辦法。如果id不連續呢？
uj5u.com熱心網友回復：
id不連續就先加一個行號
uj5u.com熱心網友回復：
直接執行這里的陳述句，把這里的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)
uj5u.com熱心網友回復：
借用樓上幾位的勞動成果，這樣就比較完美了。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) ;
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/172561.html
標籤：MySQL