课程表的某个SQL语句

最近帮助朋友写了一个于课程表相关的SQL语句，比较经典，简要过程如下：

需求：

       

 

在PostgreSQL测试过程如下：

    

sde=# select * from t1;
    id    | name
----------+------
 20180001 | 张三
 20180002 | 李四
 20180003 | 王五
(3 行记录)


sde=# select * from t2;
    id    | score |    time
----------+-------+------------
 20180001 |    88 | 2018-01-02
 20180002 |    61 | 2018-01-02
 20180001 |    91 | 2018-01-12
 20180002 |    79 | 2018-01-12
 20180001 |    85 | 2018-01-22
 20180002 |    98 | 2018-01-22
(6 行记录)

  第一种方法：

  

sde=#  select t3.id,t3.name,t2.score from
sde-#  (
sde(#     select c.id,c.name as name,max(c.time) as time from
sde(#     (
sde(#           select a.name as name ,a.id as id,b.score as score,b.time as time
sde(#           from t1 a left join t2 b on a.id=b.id
sde(#     )
sde(#     c  group by c.id,c.name
sde(#  ) t3  left join t2 on t3.id=t2.id and t3.time=t2.time;
    id    | name | score
----------+------+-------
 20180001 | 张三 |    85
 20180002 | 李四 |    98
 20180003 | 王五 |
(3 行记录)

  第二种方法（使用with方法）：

  

sde=#  with c as
sde-# (
sde(#           select a.name as name ,a.id as id,b.score as score,b.time as time
sde(#           from t1 a left join t2 b on a.id=b.id
sde(#         ),
sde-#         t3 as
sde-#         (
sde(#             select c.id,c.name as name,max(c.time) as time
sde(#             from c group by c.id,c.name
sde(#         )
sde-#         select t3.id,t3.name,t2.score
sde-#         from t3 left join t2 on t3.id=t2.id and t3.time=t2.time;
    id    | name | score
----------+------+-------
 20180001 | 张三 |    85
 20180002 | 李四 |    98
 20180003 | 王五 |
(3 行记录)