Turn to the great god mysql query data occurrence interval number last 50 points-CodePudding

Turn to the great god

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

I want to achieve language sentence is the longest num of interval number, the id of the starting and ending the id, the above results to

13 the longest interval 7 start id6 end id13
24 the longest interval 7 start id4 end id11
12...
33...
43...
. The number of other
Could you tell me how to write the MYSQL to

CodePudding user response:

Didn't see your requirements, what is the longest interval?

CodePudding user response:

reference 1/f, silver point response:

didn't see your need, what is the longest interval?

Such as number 13 from the second line 13 is the interval of 4 second third 13 13 distance between a 7 lines, take the longest seven lines between the figures, began to end id13 id6

CodePudding user response:

The select num, Max (id) - min (id) + 1 from T group by num
Don't you want is the Max - min + 1?

CodePudding user response:

 create table table1 
(id int, num int); 

Insert into table1 
Values (1, 12), (2, 13), 33 (3), (4, 24), the lancet (5), (6, 13), (7), 56,,87 (8), (9, 27), 
.95 (10), (11, 24), (12.95), (13, 13); 

Select the num, group_concat (id order by id) as idstr from table1 group by num

- can solve half way, your business side take idstr string parsing once again take a string, the biggest difference between

CodePudding user response:

The select t3. * from (
The select t1. Num, min (t2) id) - t1. Id len, t1. Id start_id, min (t2) id) end_id from table1 t1
Left the join table1 t2 on t1. Num=t2. The num and t2. Id & gt; T1. Id
Where t2. The id is not null
Group by t1. Id, t1. Num
The order by t1. Num asc, len desc
) t3
Group by t3. Num

But there is a problem, the high version of the select value must be within the group by,

CodePudding user response:

reference 5 floor evanweng reply:

select t3. * from (
The select t1. Num, min (t2) id) - t1. Id len, t1. Id start_id, min (t2) id) end_id from table1 t1
Left the join table1 t2 on t1. Num=t2. The num and t2. Id & gt; T1. Id
Where t2. The id is not null
Group by t1. Id, t1. Num
The order by t1. Num asc, len desc
) t3
Group by t3. Num

But there is a problem, the high version of the select value must be within the group by,

The select t1. Num, min (t2) id) - t1. Id len, t1. Id start_id, min (t2) id) end_id from table1 t1
Left the join table1 t2 on t1. Num=t2. The num and t2. Id & gt; T1. Id
Where t2. The id is not null
Group by t1. Id, t1. Num
The order by t1. Num asc, len desc
Or query after coming out, through group num in your code, and then take the first data

CodePudding user response:

refer to 7th floor evanweng response:

Quote: refer to the fifth floor evanweng reply:
select t3. * from (
The select t1. Num, min (t2) id) - t1. Id len, t1. Id start_id, min (t2) id) end_id from table1 t1
Left the join table1 t2 on t1. Num=t2. The num and t2. Id & gt; T1. Id
Where t2. The id is not null
Group by t1. Id, t1. Num
The order by t1. Num asc, len desc
) t3
Group by t3. Num

But there is a problem, the high version of the select value must be within the group by,

The select t1. Num, min (t2) id) - t1. Id len, t1. Id start_id, min (t2) id) end_id from table1 t1
Left the join table1 t2 on t1. Num=t2. The num and t2. Id & gt; T1. Id
Where t2. The id is not null
Group by t1. Id, t1. Num
The order by t1. Num asc, len desc
Or like this query after coming out, through group num in your code, and then take the first data

Id is continuous, you this is a good way, if the id of discontinuity?

CodePudding user response:

refer to the eighth floor silver point response:

Quote: refer to 7th floor evanweng response:

Quote: refer to the fifth floor evanweng reply:
select t3. * from (
The select t1. Num, min (t2) id) - t1. Id len, t1. Id start_id, min (t2) id) end_id from table1 t1
Left the join table1 t2 on t1. Num=t2. The num and t2. Id & gt; T1. Id
Where t2. The id is not null
Group by t1. Id, t1. Num
The order by t1. Num asc, len desc
) t3
Group by t3. Num

But there is a problem, the high version of the select value must be within the group by,

The select t1. Num, min (t2) id) - t1. Id len, t1. Id start_id, min (t2) id) end_id from table1 t1
Left the join table1 t2 on t1. Num=t2. The num and t2. Id & gt; T1. Id
Where t2. The id is not null
Group by t1. Id, t1. Num
The order by t1. Num asc, len desc
Or like this query after coming out, through group num in your code, and then take the first data

Id is continuous, you this is a good way, if the id of discontinuity?

Id discontinuous to add a line number

CodePudding user response:

Directly execute the statement here, here the UNION ALL relevant content, replace your watch,
SELECT T.I D, T.N UM, T.N ` longest interval start ID `, T.N 2 ` ID ` end of the longest intervals, T.N 2 - T.N ` longest interval ` FROM (SELECT T1. * (SELECT MIN (T2) N) FROM (
SELECT T.I D, T.N UM, @ N:=@ the N + 1 N the FROM (
SELECT T. *, @ N:=0 the 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
nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull