MAX DATE带中间表 [英] MAX DATE with intermediate table

问题描述

我无法得到正确的结果。我有4个表：

I can't get the right results. I have 4 tables:

table: Aluno
id_aluno    nome
1           Bruno
2           Carlos

table: Serie
id_serie    id_aluno    descricao
1           1           Tipo A
2           1           Tipo B
3           2           Tipo A

table: Treino
id_treino   id_serie    data
1           1           2015-12-10
2           2           2015-12-12
3           3           2015-12-10

table: Avaliacao
id_avaliacao   id_aluno   data_avaliacao
1              1          2015-12-07
2              1          2015-12-01
3              2          2015-12-05
4              2          2015-12-04

我想要以下结果：

nome     descricao    data          data_avaliacao
Bruno    TIPO B       2015-12-12    2015-12-07
Carlos   TIPO A       2015-12-10    2015-12-05

Th e的问题是GROUP BY子句应该有列id_aluno，但它不是具有日期的表的外键。它们之间有一个中间表（系列）。
我还有另一个表（avaliacao），我也想要最大日期，但是当我加入它们时，我得到了一个以上的结果aluno。

The problem is that the GROUP BY clause should have column "id_aluno" but it's not foreign key of the table which has the date. There is a intermediate table between them (serie). And I have this other table (avaliacao) which I also want the max DATE, but when I join them all, I got more than one result by aluno.

查询我试过：

SELECT a.nome, s.descricao, t.data, aa.data_avaliacao FROM Aluno a JOIN Serie s ON s.id_aluno = a.id_aluno JOIN Treino t ON t.id_serie = s.id_serie JOIN Avaliacao aa ON aa.id_aluno = a.id_aluno WHERE t.data = SELECT MAX(t1.data) FROM Aluno a1 JOIN Serie s1 ON s1.id_aluno = a1.id_aluno JOIN Treino t1 ON t1.id_serie = s1.id_serie WHERE s1.id_aluno = s.id_aluno )

推荐答案

我认为davejal的代码只有在数据的最后一个数据周期的时候才符合，每个aluno的MAX日期与MAX avacacao相对应，当不这样的时候，结果不会是预期的。使用

I think that the code of davejal works only if coincides that in the data the MAX date of the table treino correspond to MAX date of table avaliacao for every aluno, when don´t be like that the results won´t be the expected. Using the idea of

草莓

Strawberry

，最后将是

SELECT a.nome
     , s.descricao
     , t.data
     , v.data_avaliacao
  FROM aluno a
  JOIN serie s
    ON s.id_aluno = a.id_aluno
  JOIN treino t
    ON t.id_serie = s.id_serie
  JOIN 
     ( SELECT s.id_aluno
            , MAX(t.data) max_data 
         FROM serie s 
         JOIN treino t 
           ON t.id_serie = s.id_serie 
        GROUP 
           BY id_aluno
     ) x
    ON x.id_aluno = s.id_aluno
   AND x.max_data = t.data
JOIN avaliacao v
        ON s.id_aluno = v.id_aluno
WHERE v.data_avaliacao IN
   ( SELECT MAX(v.data_avaliacao) max_data1 
                FROM avaliacao v
        GROUP 
           BY id_aluno
     ) 

，希望这个帮助

这篇关于MAX DATE带中间表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！