tl;dr
This is a case that is solved well with JOIN
, after all all all tables have a very regular structure, and a very direct relationship:
SELECT nome,
COUNT(*) AS atendidas,
SUM(pontuacao.valor - padraocargo.valor) AS excedente
FROM funcionario
JOIN pontuacao ON codigo = codfunc
JOIN padraocargo USING(requisito)
WHERE pontuacao.valor >= padraocargo.valor
GROUP BY codigo
ORDER BY COUNT(*) DESC,
SUM(pontuacao.valor - padraocargo.valor) DESC
;
Exit:
nome atendidas excedente
JOSÉ 3 42
JOÃO 2 55
See working on SQL Fiddle.
(at the end of the reply has a version that accounts for the surplus otherwise)
Understanding:
First of all, we have to relate the tables:
JOIN pontuacao ON codigo = codfunc
JOIN padraocargo USING(requisito)
In the case of pontuacao
, we want the specific score of each employee, and the fields have different names, so we use ON
. In the case of requisito
, as the field is equal both in scoring and in padraocargo
, we can choose to USING()
.
Until then, see our JOIN
working: (fiddle)
nome pontuacao desejado
JOSÉ 67 40
JOSÉ 56 48
JOSÉ 76 69
JOÃO 56 40
JOÃO 87 48
JOÃO 34 69
As we look for cases where the score exceeds the standard, we use this condition:
WHERE pontuacao.valor >= padraocargo.valor
Resulting in:
nome pontuacao desejado
JOSÉ 67 40
JOSÉ 56 48
JOSÉ 76 69
JOÃO 56 40
JOÃO 87 48
But we don’t want to compare line by line. We want to know who is more qualified only, so we do one grouping per employee:
GROUP BY codigo
The field codigo
is the employee ID, so we use it as a criterion. This will hide the lines, so we need to return in another way:
SELECT nome,
COUNT(*) AS atendidas
And to help in reading, we sort starting with the most punctuated:
ORDER BY COUNT(*) DESC
Okay, we have resolved what was asked in the question. But there may happen to be a "draw" in the sense that more than one of the employees meet the requirement. To improve the ranking, we can return the excess points in this way:
SUM(pontuacao.valor - padraocargo.valor) AS excedente
And, of course, order as a second criterion:
ORDER BY COUNT(*) DESC,
SUM(pontuacao.valor - padraocargo.valor) DESC
Note that the surplus is the one he passed. It has an interesting output if you want to know the general surplus. I won’t go into too much detail not to confuse, but in short it’s just:
- Take out the condition
WHERE
- Barter
COUNT(*) AS atendidas por SUM(IF(pontuacao.valor >= padraocargo.valor,1,0)) AS atendidas
In this way, the surplus will account for what was not met also. (fiddle)
If you want to return only employees who pass all criteria, you can do so:
- Take away the ordination by
atendidas
, since only the complete
- Add a
HAVING COUNT(*) = 3
Okay, but what if each cargo has different quantities of requirements?
Do a query first to get the value:
SELECT @numreq = COUNT(*) FROM padraocargo WHERE cargo = (id do cargo);
Add a HAVING COUNT(*) = @numreq
at the end of the second query
Further reading:
What is the difference between INNER JOIN and OUTER JOIN?
only José meets the requirements, what is this assessment?
– user60252
If you compare in the Score Table, Joseph is Code 1, he has respectively 67, 56, 76 in requirements 1,2,3. The requirement for José’s position according to the Standard Table Position is 40, 48, 69 respectively in requirements 1,2,3. then José has more than required by the position, in the case of João, code 2 in the Scoring Table.. it does not meet requirement 2, as it has 34 and the required in the Standard Positions table is 48.
– Dunga Cardoso
now I understand
– user60252