mysql - 左外连接与情况

我的数据库有三个表，

Studentdata with columns studentid , studentname
Assessmentdata with columns studentid, attemptedondate
Activitydata with columns studentid, date

并且每个表行每天都将使用当前时间戳进行更新。
需要帮助识别，Mostrecentdate列应该在比较两个不同表和两个不同列中的attemptedodate和date这两列之后获取日期。如果activitydata和assessmentdata中的列都为空，则在预期输出中打印学生id，Mostrecentdate为空，如图所示。
左外连接应该与studentdata表一起，我应该在哪里编写它？
预期产出应该是，

Studentid   studentname      mostrecentdate
 1           abc            2013-06-01 12:05
 2           def            2013-05-02 02:03
 3           kjr                  null

My current query is:
select S.StudentId,
S.StudentAccountName,
CASE WHEN Max(D.attemptedondate) >= Max(A.date) THEN Max(D.attemptedondate)
     ELSE Max(A.date) END
 as MOSTRECENTDATE
from activitydata A
join Studentdata S on A.StudentId=s.StudentID
join Assessmentdata D on S. StudentID =D. StudentID
 group by S.StudentId,
S.StudentAccountName

最佳答案

尝试

SELECT s.studentid, s.studentname,
       NULLIF(GREATEST(COALESCE(a.max_date, 0),
                       COALESCE(b.max_date, 0)), 0) mostrecentdate
  FROM Studentdata s LEFT JOIN
(
  SELECT studentid, MAX(attemptedondate) max_date
    FROM Assessmentdata
   GROUP BY studentid
) a ON s.studentid = a.studentid LEFT JOIN
(
  SELECT studentid, MAX(date) max_date
    FROM Activitydata
   GROUP BY studentid
) b ON s.studentid = b.studentid

或

SELECT s.studentid, s.studentname, mostrecentdate
  FROM Studentdata s LEFT JOIN
(
  SELECT studentid, MAX(max_date) mostrecentdate
    FROM
  (
    SELECT studentid, MAX(attemptedondate) max_date
      FROM Assessmentdata
     GROUP BY studentid
     UNION ALL
    SELECT studentid, MAX(date) max_date
      FROM Activitydata
     GROUP BY studentid
  ) a
   GROUP BY studentid
) b ON s.studentid = b.studentid

样本输出：
|STUDENTID | STUDENTNAME |最新日期|
-------------------------------------------------
|1 | abc | 2013-06-01 12:05:00|
|2 |定义| 2013-05-02 02:03:00|
|3 | kjr |（空）|
这里是SQLFiddle演示

关于mysql - 左外连接与情况，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/17271349/