Writing for mysql statement-CodePudding

SQL record is as follows:

Id value
1 1234
2 1234
3 1235
4 234
5 235
6 235
7 237
8 1234
9 1234
10 1234
.

Now want to statistical value is less than 3 in record some times, but is only a continuous, such as record ID4-7 is 3 digits, statistics, only once, so on, for writing SQL, thank you.

CodePudding user response:

Join queries, digits, not three, if the next record is not; A record not only remove the three, and the record of this article is the number of article 3,
The implementation code below

 create table seq (id int, value int); 
Insert into seq 
Values (1123), 
(2123), 
(4) 3123, 
(4123), 
(5123), 
(4) 6123, 
(7123), 
(8123), 
(4) 9123, 
(10123) 
/* above for the test data, the following is the implementation code */
Select count (1) the from seq as s 
Left the join (select id, length (value) as v from seq) as b on s.i d=b.i d - 1 
Where the length (s.v alue) & lt;> Ifnull (b.v, 0) and length (s.v alue)=3

CodePudding user response:

 SELECT count (mid) from (
The SELECT min (id) as mid FROM test where LENGTH (VALUE) & lt; 4 
GROUP BY VALUE 
HAVING a count (VALUE) & gt; 1) a

feasible