Doubt with query in MYSQL

I will try to explain my doubt in a simple way since it is something complex.

I have a table called formacion with the fields ID , Tipo , Categoría and SubCategoria

A table called alumnos with the fields NIA and apellidos_nombre .

A table called alumnos_formacion with the fields NIA e idFormacion . Here I keep the child of the student and their training id you have. NIA foreign key to NIA of table alumnos e idFormacion foreign key to idFormacion of table formacion

Example:

NIA:1234  IdFormacion:1 

NIA:1234  IdFormacion:3 

NIA:1234  IdFormacion:4

Finally I have a table called Ofertas with a series of fields between what interests me are:

requisito1 , requisito2 , requisito3 , requisito4 , requisito5 , requisito6 , requisito7 , requisito8

These fields contain foreign keys to the id in the table formacion

What I am trying to do is a query SQL that brings me the name of the students whose training matches at least one requirement of the offer, the one that meets the most requirements will appear first.

I give an example:

Alumno 1 with NIA: 1234 have the formations: id1 , id2 , id3

Alumno 2 with NIA: 4321 have the formations: id3 , id4 , id5

The offer requests the training: id1 , id4 , id5

In this case the name of the Alumno2 should appear first and the Alumno1 second.

I've done this query SQL but I think it has nothing to do:

SELECT DISTINCT apellidos_nombre FROM alumnos 
    INNER JOIN alumnos_formacion
        ON alumnos.nia = alumnos_formacion.nia 
    INNER JOIN ofertas
        ON alumnos_formacion.idFormacion = ofertas.requisito1 OR ofertas.requisito2 OR ofertas.requisito3 OR ofertas.requisito4 OR ofertas.requisito5 OR ofertas.requisito6 OR ofertas.requisito7 OR ofertas.requisito8
WHERE idOferta = 1