The data table finds a piece of data to find a reasonable table structure according to two range conditions (two range values respectively).

there is a requirement to locate a piece of data based on two scope conditions

for example:
there is a school that divides classes according to height and weight (all if you don"t take it seriously)

< table > < thead > < tr > < th > class < / th > < th > height_start < / th > < th > height_end < / th > < th > weight_start < / th > < th > weigth_end < / th > < / tr > < / thead > < tbody > < tr > < td > Class 1 < / td > < td > 1.5m < / td > < td > 1.9m < / td > < td > 80kg < / td > < td > 90kg < / td > < / tr > < tr > < td > Class 2 < / td > < td > 1.0m < / td > < td > 1.49m < / td > < td > 40kg < / td > < td > 79kg < / td > < / tr > < tr > < td > Class 3 < / td > < td > 1.5m < / td > < td > 1.9m < / td > < td > 90kg < / td > < td > 100kg < / td > < / tr > < / tbody > < / table >

Xiao Ming 1.6m 81kg is Class 1
Xiao Hong 1.6m 91kg that is Class 2

in fact, the current structure can achieve the desired effect, just because in a certain range, for example, in each height range to include all the weight range
is there a better design?

Jul.22,2021

according to the data of height and weight, if there are many duplicates in the data range, it can be split into two sub-tables. Height_range and weight_range, respectively store the smallest units of height and weight, such as

. < table > < thead > < tr > < th > id < / th > < th > start < / th > < th > end < / th > < / tr > < / thead > < tbody > < tr > < td > 1 < / td > < td > 1.0 < / td > < td > 1.49 < / td > < / tr > < tr > < td > 2 < / td > < td > 1.5 < / td > < td > 1.69 < / td > < / tr > < tr > < td > 3 < / td > < td > 1.7 < / td > < td > 1.9 < / td > < / tr > < / tbody > < / table >

the class table only needs to store the corresponding id


set @height = '1.6';
set @weight = '81';
select * from table where height_start < @height and height_end > @height and weight_start < @weight and weigth_end > @weight;

what you say if, then I also use yours if, if students lose weight or gain weight and grow taller, will the class they belong to change?

the optimization scheme can be referred to according to the demand: 1 the scope does not store the table, write it as configuration; 2 maintain your structure; 3.


< H1 > I think I can exhaust < / H1 >

I think height and weight can be exhausted, but there is not much data

Student form

height and weight are expressed as three-digit integers, 160081 represents 160cm, 81kg
< table > < thead > < tr > < th > name < / th > < th > height < / th > < th > weight < / th > < th > height_weight < / th > < / tr > < / thead > < tbody > < tr > < td > Xiao Ming < / td > < td > 1.6m < / td > < td > 81kg < / td > < td > 160081 < / td > < / tr > < tr > < td > Xiao Hong < / td > < td > 1.6m < / td > < td > 91kg < / td > < td > 160091 < / td > < / tr > < / tbody > < / table >

height and weight table

Update the class field according to the grouping logic
< table > < thead > < tr > < th > id < / th > < th > class < / th > < / tr > < / thead > < tbody > < tr > < td > 150080 < / td > < td > 0 < / td > < / tr > < tr > < td > 150081 < / td > < td > 0 < / td > < / tr > < tr > < td > 150082 < / td > < td > 0 < / td > < / tr > < tr > < td >. < / td > < td >. < / td > < / tr > < tr > < td > 160081 < / td > < td > 0 < / td > < / tr > < tr > < td >. < / td > < td >. < / td > < / tr > < tr > < td > 160091 < / td > < td > 0 < / td > < / tr > < tr > < td >. < / td > < td >. < / td > < / tr > < tr > < td > 190100 < / td > < td > 0 < / td > < / tr > < tr > < td >. < / td > < td >. < / td > < / tr > < / tbody > < / table >
MySQL Query : SELECT * FROM `codeshelper`.`v9_news` WHERE status=99 AND catid='6' ORDER BY rand() LIMIT 5
MySQL Error : Disk full (/tmp/#sql-temptable-64f5-7ad6a3-14b8a.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")
MySQL Errno : 1021
Message : Disk full (/tmp/#sql-temptable-64f5-7ad6a3-14b8a.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")
Need Help?