[笔记] SQL join/on/ROW_NUMBER
- inner join
- left/right outer join
- full outer join
- cross join (或不指定 join 关键字)
- on 条件用于过滤
- ROW_NUMBER
inner join
inner join 可以简写为 join
产生两张表满足 on 条件的记录的交集
select
*
from
table_a
join
table_b
on
table_a.id = table_b.id
on 条件也可以是不等式
select
*
from
table_a
join
table_b
on
table_a.id != table_b.id
不等于这种方式比较少用
left/right outer join
left/right outer join 可以简写为 left/right join
以 left join 为例子，左表必然出现在结果中，
对于右表存在满足 on 条件的记录的，取交集，和 inner join 一样，
对于右表不存在满足 on 条件的记录的，取左表部分 + 补全 null
select
*
from
table_a
left join
table_b
on
table_a.id = table_b.id
right join 则相反
full outer join
full outer join 可以简写为 full join，
对于满足 on 条件的记录的，取交集，和 inner join 一样，
对于右表不存在满足 on 条件的记录的，取左表部分 + 补全 null，和 left join 一样，
对于左表不存在满足 on 条件的记录的，取右表部分 + 补全 null，和 right join 一样
select
*
from
table_a
full join
table_b
on
table_a.id = table_b.id
相当于 left join 和 right join 的结合
cross join (或不指定 join 关键字)
把 table_a 和 table_b 的数据进行一个 N*M 的组合
select
*
from
table_a
cross join
table_b
或者
select
*
from
table_a, table_b
不需要加 on 条件，加上会报错
on 条件用于过滤
on 条件除了做链接用，也可以做筛选用，
对于 inner join 过滤条件放在 on 还是 where 效果一样，而 outer join 效果不一样，
比如 left join 的左表有记录 a 而右表有记录 b 并且能满足 on 条件，如果是在 where 过滤比如
where table_right.name != 'xxxx'
那么 a 记录和 b 记录都不会出现在结果中，因为是先 join 再过滤，
而如果是在 on 过滤比如
on table_right.name != 'xxxx'
那么 a 记录依然出现在结果中，只是右表部分用 null 补全，因为先过滤掉 b 记录再 left join
ROW_NUMBER
ROW_NUMBER() OVER(
[PARTITION BY column_1, column_2,…]
[ORDER BY column_3,column_4,…]
)
按字段分组，然后组内排序，并按组内顺序添加组内行号列
比如下面的 SQL
SELECT
group_id,
created_time,
Row_Number() OVER (partition by group_id ORDER BY created_time desc) row_id
FROM
table_a
数据按 group_id 分组，组内按 created_time 排序，组内排序后的行号作为新字段 row_id 添加到记录中
group_id | created_time | row_id
----------+---------------------+--------
group_1 | 2021-08-06 11:13:32 | 1
group_1 | 2021-08-06 11:12:32 | 2
group_1 | 2021-08-06 11:11:32 | 3
group_2 | 2021-08-06 12:13:32 | 1
group_2 | 2021-08-06 12:12:32 | 2
group_3 | 2021-08-06 13:13:32 | 1