1.简介
CROSS JOIN是sql中的一种连接方式,区别于内连接和外连接,对于cross join连接来说,其实使用的就是笛卡尔连接。在SQL中,当CROSS JOIN不使用WHERE子句时,CROSS JOIN产生了一个结果集,该结果集是两个关联表的行的乘积。通常,如果每个表分别具有n和m行,则结果集将具有n*m行;
附注:看一下官方的解释
2.简单示例
SELECT * FROM [TABLE 1] CROSS JOIN [TABLE 2]
SELECT * FROM [TABLE 1], [TABLE 2]
备注:上面两个sql等价,一般情况下,在实际开发中,我们要避免笛卡尔积。在特殊情况下,可以用一下;
3.实战
需求一:
现在有一个表student 结构如下:
id name class blood
1 张三 1 A
2 李四 2 C
3 王五 1 B
4 黄六 3 D
5 朱八 2 C
现在想查询出每个班的每种血型人数统计(假设只有ABCD四种血型)统计结果如下:
class blood num
1 A 1
1 B 1
1 C 0
1 D 0
2 A 0
2 B 0
2 C 2
2 D 0
3 A 0
3 B 0
3 C 0
3 D 1
请问如何写sql语句?
具体实现
1.建表
create table stud(
id int,
name string,
class string,
blood string
)
row format delimited fields terminated by "\t";
2.插入数据
insert into table stud
select 1,'张三','1','A' union all
select 2,'李四','2','C' union all
select 3,'王五','1','B' union all
select 4,'黄六','3','D' union all
select 5,'朱八','2','C'
3.具体sql
hive >SELECT
b.class,a.blood,COUNT(s.id) AS num
FROM
(SELECT 'A' AS blood UNION ALL
SELECT 'B' UNION ALL
SELECT 'C' UNION ALL
SELECT 'D' )a
CROSS JOIN
(SELECT DISTINCT class from stud) b
LEFT JOIN stud s
ON a.blood=s.blood AND s.class=b.class
GROUP BY b.class,a.blood
order by b.class,a.blood;
b.class a.blood num
1 A 1
1 B 1
1 C 0
1 D 0
2 A 0
2 B 0
2 C 2
2 D 0
3 A 0
3 B 0
3 C 0
3 D 1
需求二:
有这样一张表:goods_d ,表内容如下
id name note
1 jiangtai risk
2 tai rsik
有这样一张表:dim_goods_d(dt)dt='2021-04-30’的数据 如下
gid id name note
0 0 zms risk
1 1 kb risk
2 2 wd risk
3 0 jk finc
4 1 MD finc
5 2 JD finc
需求:把goods_d 的数据添加到dim_goods_d(dt)dt=‘2021-05-01’,变成如下数据
gid id name note
0 0 zms risk
1 1 kb risk
2 2 wd risk
3 0 jk finc
4 1 MD finc
5 2 JD finc
6 1 jiangtai risk
7 2 tai rsik
具体实现
insert overwrite dim_goods_d partition(dt='2021-05-01')
select
row_number() over(order by id) + ta.max_id as gid,
tb.*
from goods_d as tb
cross join
(select coalesce(max(gid),0) as max_id from dim_goods_d
where dt='2021-04-30') ta
union all
select * from dim_goods_d where dt='2021-04-30'