title: I have a question about mysql group matching score
body: <p>The table below shows the results for sql.
  The result values ​​for id '1' were grouped and outputted.</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>id</th>
  <th>class</th>
  <th>name</th>
  <th>counting</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>CLASS1</td>
  <td>A,B,C</td>
  <td>3</td>
  </tr>
  <tr>
  <td>1</td>
  <td>CLASS2</td>
  <td>AA, BB</td>
  <td>2</td>
  </tr>
  <tr>
  <td>1</td>
  <td>CLASS3</td>
  <td>AAA</td>
  <td>1</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>I want to calculate the match score using (in) condition.</p>
  <pre><code>
  SELECT SCORE FROM VIEW WHERE name IN ('A','BB','C')
  
  </code></pre>
  <p>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.</p>
  <p>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.</p>
  <p>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.</p>
  <p>I thought as above, but it is too difficult to write a sql query.</p>
  <p>give me a little tip.</p>
  <p>thank you.</p>
  <p>TEST SCHEMA <em>db&lt;&gt;fiddle <a href="https://dbfiddle.uk/?rdbms=mysql_5.7&amp;fiddle=8eac4c64561241a14d6c00e3dcd0b447" rel="nofollow noreferrer">here</a></em></p>