英文:
SQL: full outer join between two dates
问题
我有一个如下所示的表格：
┌──────┬─────┬─────┐
│ 日期 ┆ 编号 ┆ 值 │
│ --- ┆ --- ┆ --- │
│ i64 ┆ i64 ┆ i64 │
╞══════╪═════╪═════╡
│ 1 ┆ 1 ┆ 1 │
│ 1 ┆ 2 ┆ 2 │
│ 2 ┆ 2 ┆ 3 │
│ 2 ┆ 3 ┆ 4 │
│ 3 ┆ 3 ┆ 5 │
│ 3 ┆ 4 ┆ 6 │
└──────┴─────┴─────┘
我想知道如何在相同表格的基础上进行条件联接。要求如下：
1）内连接，条件是 left.date = right.date + 1
2）外连接，条件是 left.id = right.id
期望的结果如下：
date_left date_right id_left id_right v_left v_right
2 1 NULL 1 NULL 1
2 1 2 2 3 2
2 1 3 NULL 4 NULL
3 2 NULL 2 NULL 3
3 2 3 3 5 4
3 2 4 NULL 6 NULL
英文:
I have a table as follows:
┌──────┬─────┬─────┐
│ date ┆ id ┆ v │
│ --- ┆ --- ┆ --- │
│ i64 ┆ i64 ┆ i64 │
╞══════╪═════╪═════╡
│ 1 ┆ 1 ┆ 1 │
│ 1 ┆ 2 ┆ 2 │
│ 2 ┆ 2 ┆ 3 │
│ 2 ┆ 3 ┆ 4 │
│ 3 ┆ 3 ┆ 5 │
│ 3 ┆ 4 ┆ 6 │
└──────┴─────┴─────┘
I wonder if and how can I do a conditional join with itself based on date and id. The requirements are:
- inner join on left.date = right.date + 1
- outer join on left.id = right.id
The desired result would be:
date_left date_right id_left id_right v_left v_right
2 1 NULL 1 NULL 1
2 1 2 2 3 2
2 1 3 NULL 4 NULL
3 2 NULL 2 NULL 3
3 2 3 3 5 4
3 2 4 NULL 6 NULL
答案1
得分: 1
select
coalesce(l.date, r.date + 1) as date_left,
coalesce(r.date, l.date - 1) as date_right,
l.id as id_left, r.id as id_right,
l.v as v_left, r.v as v_right
from (select * from T where date > (select min(date) from T)) l
full outer join
(select * from T where date < (select max(date) from T)) r
on r.id = l.id and l.date = r.date + 1
order by date_left, date_right, coalesce(l.id, r.id);
不过滤全外连接之前，您也可以将该逻辑移到where子句中。请参考示例链接：https://dbfiddle.uk/x3mnMb-h。
英文:
select
coalesce(l.date, r.date + 1) as date_left,
coalesce(r.date, l.date - 1) as date_right,
l.id as id_left, r.id as id_right,
l.v as v_left, r.v as v_right
from (select * from T where date > (select min(date) from T)) l
full outer join
(select * from T where date < (select max(date) from T)) r
on r.id = l.id and l.date = r.date + 1
order by date_left, date_right, coalesce(l.id, r.id);
Instead of filtering prior to the full join, you could also move that logic into a where clause. Refer to fiddle for example.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论