The existing two tables
A table a scale (tens of thousands of data)
A 1233
B 2233
C 3233
Ding 4233

Another table for interval (for two-dimensional table description here, according to the actual change one dimension tables)
A 500 for grade A, grade B 1000-1500, 500-1000 grade C level 1500 + D
B 500 for grade A, grade B, 500-1500 1500-2000 - C 2500 above grade D
1000 C for grade A, grade B, 1000-2000 on more than 2000 grade D (note that no C)
D 1000 for grade A, grade B, 1000-3000 on more than 3000 grade C

How to match the
A grade B
B grade C
C grade D
D grade C

Everybody move brain, I have thought for many days...

CodePudding user response:

Is actually perform almost frequency this function in excel

CodePudding user response:

Interval table structure is
Name level start number to end number
A grade A NULL 500

 
Select * from table number as a, interval table as b 
Where a. Name=b. name and a. number & gt;=ifnull (b. began to number, a. number) and a. number & lt; Ifnull (b. end number, a. number + 1) 

CodePudding user response:

Upstairs practices can be implemented, could improve the query efficiency,
Table number unchanged, and modify the interval table, four fields:
Classification (a YiBingDing... Name), rank (ABC... ), the level range (the first level can be set to the minimum possible values, such as 1), the level range (the last interval is set to the maximum possible values, such as 999999)
Pseudo-code for reference:

 
The SELECT quant. Classification, reaching the level name 
- here use LEFT JOIN, prevent abnormal values beyond the upper and lower 
The FROM quantity table quant LEFT JOIN interval table reaching ON quant.=reaching classification. Classification 
WHERE quant. Quantity & gt;=reaching. The lower limit AND quant. Number & lt; Reaching the upper limit 

Such a change, at least can improve an order of magnitude estimate query speed,

CodePudding user response:

You have a great god said truth

Give me some inspiration

I finally decided, with
the stored procedure
Set a threshold, determine whether it is greater than the threshold

For example,
Interval table (for two-dimensional table description here, according to actual change a dimension table)
A 500 for grade A, grade B 1000-1500, 500-1000 grade C level 1500 + D

I will set a dimension table
A grade A 0
A grade B 500
A grade C 1000

Then
Table number began to judge all numerical
Table top rating from 1 to interval, cycle
Such as a 833
The first step, numerical value greater than 0 was established as A
The next step, numerical established more than 500 for B
Next, value is greater than 1000 was not
End of cycle
Found that speed is also ok, more than 200 w,

I do so with the idea is that the most afraid of users will write wrong interval, such as grade A 0-500, grade B to write A 450-1000, such like 477 is likely to misjudge

The great god, look, is this possible? What are the shortcomings

CodePudding user response:

Range, how to write wrong, your application will not control? Like to the user input range separated values only, program, or in the database automatically generate the start and end values not line? Stubbornly users to write it?

CodePudding user response:

And, of course, you are willing to use cycle is also no problem, anyway, they can achieve a goal