课程表的某个SQL语句
分享
-
2018-10-30
最近帮助朋友写了一个于课程表相关的SQL语句，比较经典，简要过程如下：
需求：
在PostgreSQL测试过程如下：
第一种方法：
第二种方法（使用with方法）：
文章来源：https://blog.csdn.net/liufeng1980423/article/details/81214188
需求：
在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 行记录)文章来源：https://blog.csdn.net/liufeng1980423/article/details/81214188
