Início / dba / Perguntas / 116156
Accepted
user4150760
Asked: 2015-09-26 07:05:56 +0800 CST

Junção esquerda não funciona com subconsulta

  • 772

Eu tenho uma tabela scoresonde cada entrada corresponde a um (student, subject)par específico.

CREATE TABLE score (
  id serial PRIMARY KEY,
  value integer NOT NULL,
  subject_id integer NOT NULL,
  student_id integer NOT NULL,
  CONSTRAINT s1_id FOREIGN KEY (subject_id) REFERENCES subject (id),
  CONSTRAINT s2_id FOREIGN KEY (student_id) REFERENCES student (id),
);

Quero escolher as 5 principais disciplinas com as pontuações gerais mais altas e, em seguida, calcular a pontuação média de cada aluno nessas 5 disciplinas. Alguns alunos podem não ter inscrições para algumas disciplinas. Esses valores receberiam uma pontuação padrão.

Aqui está o que eu tenho:

SELECT student_id, AVG(COALESCE(score.value, default_value)) FROM 
(
    SELECT score.subject_id, subject.name, SUM(score.value) AS score_sum
    FROM score
    JOIN subject on subject.id = score.subject_id
    WHERE subject.name != 'skip me'
    GROUP BY score.subject_id
    ORDER BY score_sum DESC
    LIMIT 5
) AS score_sort
LEFT JOIN score ON score_sort.subject_id = score.subject_id 
GROUP BY student_id

A consulta interna funciona corretamente para selecionar os 5 primeiros. Mas a LEFT JOINconsulta externa não seleciona as linhas em que um aluno não possui uma pontuação. O que eu estou fazendo errado aqui ?

postgresql join

1 respostas

  1. Best Answer

    Você pode usar um CROSS JOINfrom score_sortpara todos os alunos e, em seguida, uma LEFTjunção para score:

    SELECT st.student_id, 
       AVG(COALESCE(sc.value, default_value)) AS average_score
FROM 
(
    SELECT score.subject_id, subject.name, SUM(score.value) AS score_sum
    FROM score
    JOIN subject on subject.id = score.subject_id
    WHERE subject.name != 'skip me'
    GROUP BY score.subject_id
    ORDER BY score_sum DESC
    LIMIT 5
) AS score_sort AS sort 
  CROSS JOIN student AS st
  LEFT JOIN score AS sc
    ON  sort.subject_id = sc.subject_id 
    AND   st.student_id = sc.student_id
GROUP BY st.student_id ;

    Uma abordagem diferente seria alterar sua consulta para contar as pontuações disponíveis de cada aluno (nas 5 principais disciplinas):

    SELECT score.student_id, 
       (SUM(score.value) + (5 - COUNT(score.subject_id)) * default_value) / 5 
           AS average_score
FROM 
(
    SELECT score.subject_id, subject.name, SUM(score.value) AS score_sum
    FROM score
    JOIN subject on subject.id = score.subject_id
    WHERE subject.name != 'skip me'
    GROUP BY score.subject_id
    ORDER BY score_sum DESC
    LIMIT 5
) AS score_sort
JOIN score ON score_sort.subject_id = score.subject_id 
GROUP BY score.student_id ;

    Mas mesmo com isso, você ainda não obteria nenhum resultado para os alunos que não obtiverem pontuação em nenhuma das 5 principais disciplinas.

    • 3

relate perguntas