'I have a question about mysql group matching score
The table below shows the results for sql. The result values for id '1' were grouped and outputted.
| id | class | name | counting |
|---|---|---|---|
| 1 | CLASS1 | A,B,C | 3 |
| 1 | CLASS2 | AA, BB | 2 |
| 1 | CLASS3 | AAA | 1 |
I want to calculate the match score using (in) condition.
SELECT SCORE FROM VIEW WHERE name IN ('A','BB','C')
If the above sql is executed. If the counting is 3, if it matches the first name of each class, 3 points, the second is 2 points, and the last is 1 point.
If the counting is 2, I want to give 2 points if it matches the first name of each class, and 1 point for the second.
So, in the above result, A is 3 points in CLASS1, BB is 1 point in CLASS2, and C is 1 point in CLASS1, and the result is 5 points.
I thought as above, but it is too difficult to write a sql query.
give me a little tip.
thank you.
TEST SCHEMA db<>fiddle here
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|