Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SELECT DISTINCT
- a.*
- FROM
- (SELECT
- pr.Nome AS 'Prova',
- CONCAT(
- CASE WHEN (SELECT Nome FROM alunos WHERE idAlunos = pa.idAluno2) IS NULL THEN (SELECT Nome FROM alunos WHERE idAlunos = pa.idAluno1) ELSE SUBSTRING_INDEX((SELECT Nome FROM alunos WHERE idAlunos = pa.idAluno1), ' ', 1) END,
- CASE WHEN (SELECT Nome FROM alunos WHERE idAlunos = pa.idAluno2) IS NULL THEN '' ELSE ' e ' END,
- COALESCE(SUBSTRING_INDEX((SELECT Nome FROM alunos WHERE idAlunos = pa.idAluno2), ' ', 1), '')
- ) AS 'Alunos',
- CASE WHEN (SELECT Nome FROM alunos WHERE idAlunos = pa.idAluno2) IS NULL OR (SELECT idEscolas FROM alunos WHERE idAlunos = pa.idAluno1) = (SELECT idEscolas FROM alunos WHERE idAlunos = pa.idAluno2) THEN (SELECT Nome FROM escolas WHERE idEscolas = al.idEscolas) ELSE CONCAT((SELECT Nome FROM escolas WHERE idEscolas = (SELECT idEscolas FROM alunos WHERE idAlunos = pa.idAluno1)), ' e ', (SELECT Nome FROM escolas WHERE idEscolas = (SELECT idEscolas FROM alunos WHERE idAlunos = pa.idAluno2))) END AS 'Escola',
- CASE WHEN (SELECT Nome FROM alunos WHERE idAlunos = pa.idAluno2) IS NULL OR (SELECT idSeries FROM alunos WHERE idAlunos = pa.idAluno1) = (SELECT idSeries FROM alunos WHERE idAlunos = pa.idAluno2) THEN (SELECT series.Desc FROM series WHERE idSeries = al.idSeries) ELSE CONCAT((SELECT series.Desc FROM series WHERE idSeries = (SELECT idSeries FROM alunos WHERE idAlunos = pa.idAluno1)), ' e ', (SELECT series.Desc FROM series WHERE idSeries = (SELECT idSeries FROM alunos WHERE idAlunos = pa.idAluno2))) END AS 'Ano'
- FROM
- participacao pa
- INNER JOIN provas pr ON pa.idProvas = pr.idProvas
- INNER JOIN alunos al ON al.idAlunos = pa.idAluno1
- INNER JOIN escolas es ON es.idEscolas = al.idEscolas
- INNER JOIN series se ON se.idSeries = al.idSeries
- WHERE
- pr.idProvas = ${idProvas}) a,
- (SELECT P1 + P2 + P3 AS 'Score' FROM participacao) Score,
- (SELECT idProvas FROM provas) idProvas
- WHERE
- idProvas = 1
- ORDER BY idProvas ASC, Score DESC;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement