Calcular a pontuação e classificar candidatos comparando campos de tabelas

[Lúcio Flávio da Silva Sales]

Estou quase conseguindo, só falta um pequeno detalhe:

Eis a query que consegui fazer para marcar os acertos e erros:

set @nota_matematica = 0, @nota_portugues = 0, @nota_fisica = 0;

SELECT

autor.nome_autor,

resposta.Cand_id,

resposta.Prova,

IF(resposta.Resp_Questao_1 = gabarito.Solucao_Questao_1, 'CERTO' , 'ERRADO') AS Result_Questao_1,

IF(resposta.Resp_Questao_1 = gabarito.Solucao_Questao_1, @nota_fisica = @nota_fisica + 1, @nota_fisica) AS Ponto_fisica,

IF(resposta.Resp_Questao_2 = gabarito.Solucao_Questao_2, 'CERTO', 'ERRADO') AS Result_Questao_2,

IF(resposta.Resp_Questao_2 = gabarito.Solucao_Questao_2, @nota_fisica = @nota_fisica + 1, @nota_fisica) AS Ponto_fisica,

IF(resposta.Resp_Questao_3 = gabarito.Solucao_Questao_3, 'CERTO', 'ERRADO') AS Result_Questao_3,

IF(resposta.Resp_Questao_3 = gabarito.Solucao_Questao_3, @nota_matematica = @nota_matematica + 1, @nota_matematica) AS Ponto_matematica,

IF(resposta.Resp_Questao_4 = gabarito.Solucao_Questao_4, 'CERTO', 'ERRADO') AS Result_Questao_4,

IF(resposta.Resp_Questao_4 = gabarito.Solucao_Questao_4, @nota_matematica = @nota_matematica + 1, @nota_matematica) AS Ponto_matematica,

IF(resposta.Resp_Questao_5 = gabarito.Solucao_Questao_5, 'CERTO', 'ERRADO') AS Result_Questao_5

IF(resposta.Resp_Questao_5 = gabarito.Solucao_Questao_5, @nota_portugues = @nota_portugues + 1, @nota_portugues) AS Ponto_portugues,

IF(resposta.Resp_Questao_6 = gabarito.Solucao_Questao_6, 'CERTO', 'ERRADO') AS Result_Questao_6,

IF(resposta.Resp_Questao_6 = gabarito.Solucao_Questao_6, @nota_portugues = @nota_portugues + 1, @nota_portugues) AS Ponto_portugues

FROM

gabarito LEFT JOIN resposta on resposta.Prova = gabarito.Prova

LEFT JOIN autor on autor.id = resposta.Cand_id

ORDER BY

resposta.Cand_id ASC;

O detalhe que falta é a incrementação do valor da nota, que não consigo realizar. A declaração "@nota_fisica = @nota_fisica + 1" não faz efeito, torna o valor da nota igual a zero. Não consegui descobrir como incrementar valor às variáveis das notas.

---

Conteúdo inicial:

 

 

 

Tenho dificuldade em fazer a consulta com funções comparativas em SQL, de maneira a automatizar o resultado, classificando os candidatos em ordem decrescente de pontuação. No php implementei a indicação do acerto e erro, o cálculo da pontuação, mas não fica ordenado. Preciso da ajuda dos experientes. Obrigado!

autor

Id Nome Cidade UF Fone Email Senha Nascimento Experiencia Prova

1    João    Catende    PE                    NULL    0    1
2    José    Palmares    PE                    NULL    0    1
3    Lucas    Barreiros    PE                    NULL    0    1

resposta

Id    Cand_id    Prova    Tentativa    Resp_Questao_1    Resp_Questao_2    Resp_Questao_3    Resp_Questao_4    Resp_Questao_5
1    1    1    NULL    A    D    E    C    B
2    2    1    NULL    C    E    E    C    A
3    3    1    NULL    B    D    A    A    A

gabarito

Id    Prova    Solucao_Questao_1    Solucao_Questao_2    Solucao_Questao_3    Solucao_Questao_4    Solucao_Questao_5
1    1    C    D    E    C    A

Eu quero uma consulta que retorne isso:

resultado

|Cand_id | Nome | Prova | Questao_1 | Questao_2 | Questao_3 | Questao_4 | Questao_5 | Pontos |
| 2 | José |  1    | CERTO     |  ERRADO   |  CERTO    |  CERTO    | CERTO     | 4      |
| 1 | João |  1    | ERRADO    |  CERTO    |  CERTO    |  CERTO    | ERRADO    | 3      |
| 3 | Lucas|  1    | ERRADO    |  CERTO    |  ERRADO   |  ERRADO   | CERTO     | 2      |

 

A query que estou usando no php é essa:

    $sql = "SELECT

autor.nome_autor,
resposta.Cand_id,
resposta.Prova,
resposta.Resp_Questao_1,
resposta.Resp_Questao_2,
resposta.Resp_Questao_3,
resposta.Resp_Questao_4,
resposta.Resp_Questao_5,
gabarito.Solucao_Questao_1,
gabarito.Solucao_Questao_2,
gabarito.Solucao_Questao_3,
gabarito.Solucao_Questao_4,
gabarito.Solucao_Questao_5

FROM

gabarito LEFT JOIN resposta on resposta.Prova = gabarito.Prova

LEFT JOIN autor on autor.id = resposta.Cand_id

ORDER BY

resposta.Cand_id ASC";

O problema é que eu não sei como usar a função IF no sql, então não tenho como calcular a pontuação para ordenar ainda na query. Consigo utilizar a query para no php fazer as comparações entre os campos e calcular a pontuação. Mas no foreach ainda não há calculo do resultado para ordenar.
Eu só quero saber onde encaixar a função IF no sql.

 

Se alguém precisar, deixo o sql do BD:

CREATE TABLE autor
( id int(11) NOT NULL,
 nome_autor varchar(80) NOT NULL,
 cidade_autor varchar(50) DEFAULT NULL,
 uf_autor varchar(50) DEFAULT NULL,
 fone_autor varchar(40) DEFAULT NULL,
 email_autor varchar(50) NOT NULL,
 senha_autor varchar(50) NOT NULL,
 observacoes_autor varchar(50) DEFAULT NULL,
 nasc_usuario date DEFAULT NULL,
 exp_usuario int(11) DEFAULT NULL,
 tipo_prova int(11) DEFAULT NULL )
 ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- -- Extraindo dados da tabela autor

INSERT INTO autor (id, nome_autor, cidade_autor, uf_autor, fone_autor, email_autor, senha_autor, observacoes_autor, nasc_usuario, exp_usuario, tipo_prova) VALUES
(1, 'João', 'Catende', 'PE', '', '', '', '', null, 0, 1),
(2, 'José', 'Palmares', 'PE', '', '', '', '', null, 0, 1),
(3, 'Lucas', 'Barreiros', 'PE', '', '', '', '', null, 0, 1);

select * from autor;

CREATE TABLE `gabarito` (
  `Id` int(2) NOT NULL,
  `Prova` int(2) DEFAULT NULL,
  `Solucao_Questao_1` varchar(720) DEFAULT NULL,
  `Solucao_Questao_2` varchar(720) DEFAULT NULL,
  `Solucao_Questao_3` varchar(720) DEFAULT NULL,
  `Solucao_Questao_4` varchar(720) DEFAULT NULL,
  `Solucao_Questao_5` varchar(720) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;

--
-- Extraindo dados da tabela `gabarito`
--

INSERT INTO `gabarito` (`Id`, `Prova`, `Solucao_Questao_1`, `Solucao_Questao_2`, `Solucao_Questao_3`, `Solucao_Questao_4`, `Solucao_Questao_5`) VALUES
(1, 1, 'C', 'D', 'E', 'C', 'A');

select * from gabarito;

CREATE TABLE `resposta` (
  `Id` int(11) NOT NULL,
  `Cand_id` varchar(7) DEFAULT NULL,
  `Prova` varchar(5) DEFAULT NULL,
  `Tentativa` int(1) DEFAULT NULL,
  `Resp_Questao_1` varchar(614) DEFAULT NULL,
  `Resp_Questao_2` varchar(614) DEFAULT NULL,
  `Resp_Questao_3` varchar(614) DEFAULT NULL,
  `Resp_Questao_4` varchar(614) DEFAULT NULL,
  `Resp_Questao_5` varchar(614) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;

--
-- Extraindo dados da tabela `resposta`
--

INSERT INTO `resposta` (`Id`, `Cand_id`, `Prova`, `Tentativa`, `Resp_Questao_1`, `Resp_Questao_2`, `Resp_Questao_3`, `Resp_Questao_4`, `Resp_Questao_5`) VALUES
(1, '1', '1', null, 'A', 'D', 'E', 'C', 'B'),
(2, '2', '1', null, 'C', 'E', 'E', 'C', 'A'),
(3, '3', '1', null, 'B', 'D', 'A', 'A', 'A');

select * from resposta;

 

 

Editado Setembro 2 por Lúcio Flávio da Silva Sales

[Frank K Hosaka]

CREATE DEFINER=`root`@`localhost` PROCEDURE `calcular_notas`()
BEGIN

DROP TABLE IF EXISTS aluno;
DROP TABLE IF EXISTS resposta;
DROP TABLE IF EXISTS gabarito;

CREATE TABLE `aluno` (
  `id` int NOT NULL AUTO_INCREMENT,
  `nome` varchar(80) COLLATE utf8mb4_general_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `resposta` (
  `Id` int NOT NULL AUTO_INCREMENT,
  `Aluno_id` int DEFAULT NULL,
  `Questao` int DEFAULT NULL,
  `Resposta` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `gabarito` (
  `Id` int NOT NULL AUTO_INCREMENT,
  `Questao` int DEFAULT NULL,
  `Solucao` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO aluno (Nome) values ('João'),('José'),('Lucas');
INSERT INTO resposta (Aluno_Id,Questao,Resposta) values (1,1,'A'),(1,2,'D'),(1,3,'E'),(1,4,'C'),(1,5,'B'),
	(2,1,'C'),(2,2,'E'),(2,3,'E'),(2,4,'C'),(2,5,'A'),(3,1,'B'),(3,2,'D'),(3,3,'A'),(3,4,'A'),(3,5,'A');
INSERT INTO gabarito (Questao, Solucao) VALUES  (1, 'C'),(2, 'D'),(3, 'E'),(4,'C'),(5,'A');
SELECT
    r.Aluno_Id,
    a.Nome,
    COUNT(*) AS acertos
FROM
    resposta r
JOIN
    gabarito g ON r.Questao = g.Questao
JOIN
	aluno a ON a.id = r.Aluno_id
WHERE
    r.Resposta = g.Solucao
GROUP BY
    r.Aluno_Id;

END