时间戳

首页 > 解决方案 > 使用空值连接两个表 - SQL

sql - 使用空值连接两个表 - SQL

问题描述

我有两个表如下。我想在 2/04/2021 日期加入这两个，就像在输出表中一样。如输出所示，有机会获得任何表的空值。

学生详情

Auto_id    StudentID    StudentName         LastAttendedDate
1            AAA          RAGHAV           8/25/2020 1:40:35 AM
2            BBB          VARUN            2/04/2021 5:40:23 AM
3            AAA          RAGHAV           2/04/2021 1:40:25 AM
4            AAA          RAGHAV           2/04/2021 3:40:35 AM
5            BBB          VARUN            10/01/2021 9:40:35 AM
6            CCC          MAYA             2/04/2021 8:40:35 AM

考试详情

Auto_id   StudentID      exam_name          exam_date  
1            AAA          PRILIMS          2/04/2021 1:40:35 AM 
2            BBB          MAINS            2/04/2021 5:40:35 AM
3            AAA          PRILIMS          2/04/2021 8:40:35 AM 
4            GGG          PRILIMS          2/04/2021 1:10:35 AM 
5            GGG          MAINS            2/04/2021 2:10:35 AM 
6            GGG          MAINS            2/05/2021 2:10:35 AM

输出

StudentID        LastAttendedDate          exam_name      exam_date          
  BBB          2/04/2021 5:40:23 AM         MAINS        2/04/2021 5:40:35 AM
  AAA          2/04/2021 1:40:25 AM         PRILIMS      2/04/2021 8:40:35 AM
  AAA          2/04/2021 3:40:35 AM         MAINS        2/04/2021 8:40:35 AM
  CCC          2/04/2021 8:40:35 AM          NULL          NULL
  GGG              NULL                      MAINS       2/04/2021 2:10:35 AM

标签： sqlsql-server-2008

解决方案

我想你想要full join，但在聚合两个表之后：

select coalesce(sd.studentid, ed.studentid) as studentid,
       sd.studentname, sd.lastattendeddate,
       ed.exam_name, ed.exam_date
from (select studentid, studentname, max(lastattendeddate) as lastattendeddate
      from student_details
      group by studentid, studentname
     ) sd full join
     (select studentid, exam_name, max(exam_adte) as exam_date
      from exam_details
      group by studentid, exam_name
     ) ed
     on sd.studentid = ed.studentid;

推荐阅读