Field number (8) state (occupy, free)
Number of data sets: 1.8 million, the total is a serial number, for example (44210000 ~ 46009999)
1. Ask the number set thousands of them roughly the first number
Description: 1000 consecutive state for the number of "free" all set, output the first number (such as 44250000 ~ 44250999 all state for leisure, output 44250000)
2. Ask the number set hundreds of them roughly the first number
Description: in the thousands of them roughly screening, 100 state straight for the number of "free" set, output the first number (such as 44250000 ~ 44250099 all state for leisure, output 44250000)
3. Please set the whole ten them roughly the number the first number
Description: in the hundreds of them roughly screening, continuous 10 state for the number of "free" all set, output the first number (such as 44250000 ~ 44250009 all state for leisure, output 44250000)
Ask everybody a great god help to see what is better solution, I wrote in logical, poor performance
CodePudding user response:
Thousands of them roughly, for exampleSolution a:
Suggested the state: 0 idle, 1, status value added and screening of 0 idle period,
SELECT number, the SUM (state) FROM table
GROUP BY SUBSTR (number, 1, 5)
HAVING the SUM (state)=0
Scheme 2:
Screening of all free first, and then grouping the count number for 1000,
SELECT number, count (number) FROM table
WHERE state='free'
GROUP BY SUBSTR (number, 1, 5)
HAVING a count (number)=1000
Hundreds of them roughly is GROUP BY SUBSTR (number, 1, 6)
CodePudding user response:
"How to assist MySQL implement Oracle advanced analysis function" in this article is how to realize the MySQL nested functions, performance is relatively highCodePudding user response:
MySQL nested functions, it is difficult to implement, especially when large amount of data, you can refer to this article introduced:"How to assist MySQL implement Oracle advanced analysis function"
CodePudding user response:
Them roughly the first number only consider the whole round it