There are two tables a and b, two tables, now need a query the student scores in the table based on the actual scores of

A table
The name score
Zhang SAN 55
Li si 60
Fifty and 85


Table b
Score the percentage %
0 0
Twenty five
40 10
60 40
60 80
100, 80,

Now need to check that they actually received scores, for example:
1. Zhang SAN score 55 in table b is greater than the proportion of less than 60 40 for 10 therefore zhang SAN to get the actual scores for 55 * 10%=5.5
2. Li si score 60 in table b is greater than or equal to 60 proportion of less than 80 to 40 so zhang SAN to get the actual scores of 60 * 40%=24
To get the result of the following
The name score actually score
Zhang SAN 55 5.5
Li si 60 24

CodePudding user response:

Select a.n ame, a. score, Max proportion (b. a. score * %)
From a, b
Where a. Score & gt;=b. score
Group by the name