Início / dba / Perguntas / 153528
Accepted
Nishant
Asked: 2016-10-28 12:12:32 +0800 CST

Como identifico todos os alunos aprovados?

  • 772

Suponha que eu tenha uma Tabela studente uma Tabela examque rastreie todos os exames que um aluno fez, como faço para encontrar todos os alunos que passaram ou não fizeram nenhum exame semelhante a uma lista branca? Neste exemplo, isso significa os alunos 1 e 2.

PS: passsinalizador Usignifica que o status é desconhecido, activesinalizador Nsignifica que o exame foi removido, o restante dos sinalizadores é autoexplicativo.

Só para deixar bem claro, o caso de uso real é, na verdade assessments, preciso descobrir as pessoas que não têm pendingavaliações para participar, para que possam prosseguir com o próximo conjunto de procedimentos. Outros precisariam terminar os itens pendentes.

+-------------+------+
| student_idn | name |
+-------------+------+
| 1           | Mark |
| 2           | Jack |
| 3           | Jane |
| 4           | Jill |
+-------------+------+

+----------+-------------+------+--------+
| exam_idn | student_idn | pass | active |
+----------+-------------+------+--------+
| 1        | 1           | Y    | Y      |
| 2        | 1           | Y    | Y      |
| 3        | 1           | Y    | Y      |
| 4        | 2           | Y    | Y      |
| 5        | 2           | N    | N      |
| 6        | 3           | Y    | Y      |
| 7        | 3           | Y    | Y      |
| 8        | 3           | Y    | Y      |
| 9        | 3           | N    | Y      |
| 10       | 4           | U    | Y      |
+----------+-------------+------+--------+

Eu vim com esta consulta abaixo, mas existe uma maneira melhor de fazer isso? Eu sinto que isso é O(n) ou O(nlogn) dependendo do índice etc. não tenho certeza (alguém sabe o melhor possível para isso), portanto, deve estar próximo do ideal?

SELECT
  *
FROM student
WHERE NOT EXISTS (SELECT
  *
FROM exam
WHERE exam.student_idn = student.student_idn
AND pass <> 'Y'
AND active = 'Y')
if-not-exists

2 respostas

  1. Best Answer

    O problema que você descreve é ​​conhecido como divisão relacional. Existem basicamente duas maneiras de abordá-lo:

    1. Transforme para todo x : p(x) em não existe x : não p(x)

    Ou seja, transformar alunos que passaram em todas as provas para alunos onde não existe uma prova que o aluno não passou. Sua consulta na pergunta é um exemplo disso.

    Essa transformação costuma ser chamada de De Morgan's laws for quantifiers. Veja por exemplo:

    https://philosophy.stackexchange.com/questions/28324/proving-de-morgans-laws-for-quantifiers

    1. Alunos que passaram a mesma quantidade de testes que há testes. Algo como:

       SELECT student_id
 FROM tests
 WHERE passed = 'y' 
 GROUP BY student_id
 HAVING COUNT(1) = (SELECT COUNT(DISTINCT test_id) 
                    FROM tests)

    Estou respondendo de um celular, então não consigo ver a pergunta enquanto respondo, então os detalhes da minha resposta provavelmente estão errados de acordo com sua pergunta, mas espero que você tenha uma ideia.

    • 3

  2. Que tal agora:

    USE tempdb

CREATE TABLE dbo.Student (
  s_idn int,
  name varchar(30)
);
CREATE TABLE dbo.Exam (
  e_idn int IDENTITY,
  s_idn int,
  pass char(1),
  active char(1)
)
GO
INSERT INTO dbo.student (s_idn, name)
  VALUES (1, 'Mark'),
  (2, 'Jack'),
  (3, 'Jane'),
  (4, 'Jill')
GO

INSERT INTO dbo.Exam (s_idn, pass, active)
  VALUES (1, 'Y', 'Y')
  , (1, 'Y', 'Y')
  , (1, 'Y', 'Y')
  , (2, 'Y', 'Y')
  , (2, 'N', 'N')
  , (3, 'Y', 'Y')
  , (3, 'Y', 'Y')
  , (3, 'Y', 'Y')
  , (3, 'N', 'Y')
  , (4, 'U', 'Y')
GO

SELECT
  *
FROM dbo.Student
EXCEPT
SELECT DISTINCT
  s.*
FROM dbo.student s
INNER JOIN dbo.exam e
  ON s.s_idn = e.s_idn
  AND (e.pass <> 'Y'
  AND e.active <> 'N');

    O seguinte é menos dispendioso em desempenho:

    SELECT
  *
FROM dbo.student
WHERE s_idn NOT IN (SELECT s.s_idn
                    FROM dbo.student s
                    INNER JOIN dbo.exam e
                        ON s.s_idn = e.s_idn
                       AND e.pass <> 'Y'
                       AND e.active <> 'N')

    Na verdade deveria ser assim, dessa forma não precisa juntar as duas tabelas:

    SELECT
  *
FROM dbo.student
WHERE s_idn NOT IN (SELECT s_idn
                    FROM dbo.exam e
                    WHERE e.pass <> 'Y'
                      AND e.active = 'Y')
    • 1

relate perguntas