clickhouse连接查询

连接查询

create table yg(
id Int8,
name String,
age UInt8,
bid Int8
)engine=Log;

insert into yg values
(1,'aa',23,1),
(2,'bb',24,2),
(3,'cc',27,1),
(4,'dd',13,3),
(5,'ee',53,3),
(6,'ff',33,3);

select * from yg;
┌─id─┬─name─┬─age─┬─bid─┐
│  1 │ aa   │  23 │   1 │
│  2 │ bb   │  24 │   2 │
│  3 │ cc   │  27 │   1 │
│  4 │ dd   │  13 │   3 │
│  5 │ ee   │  53 │   3 │
│  6 │ ff   │  33 │   3 │
└────┴──────┴─────┴─────┘

create table bm(
bid Int8,
name String
)engine=Log;

insert into bm values(1,'x'),(2,'y'),(3,'z');
select * from bm;
┌─bid─┬─name─┐
│   1 │ x    │
│   2 │ y    │
│   3 │ z    │
└─────┴──────┘

create table gz(
id Int8,
jb Int64,
jj Int64
)engine=Log;

insert into gz values(1,1000,2000),(1,1000,2000),(2,2000,1233),(3,2000,3000),(4,4000,1000),(5,5000,2000);
select * from gz;
┌─id─┬───jb─┬───jj─┐
│  1 │ 1000 │ 2000 │
│  1 │ 1000 │ 2000 │
│  2 │ 2000 │ 1233 │
│  3 │ 2000 │ 3000 │
│  4 │ 4000 │ 1000 │
│  5 │ 5000 │ 2000 │
└────┴──────┴──────┘

all

select * from yg [all] join gz on yg.id=gz.id;   //all是默认的关联精度，所有符合条件的都关联上
┌─id─┬─name─┬─age─┬─bid─┬─gz.id─┬───jb─┬───jj─┐
│  1 │ aa   │  23 │   1 │     1 │ 1000 │ 2000 │
│  1 │ aa   │  23 │   1 │     1 │ 1000 │ 2000 │
│  2 │ bb   │  24 │   2 │     2 │ 2000 │ 1233 │
│  3 │ cc   │  27 │   1 │     3 │ 2000 │ 3000 │
│  4 │ dd   │  13 │   3 │     4 │ 4000 │ 1000 │
│  5 │ ee   │  53 │   3 │     5 │ 5000 │ 2000 │
└────┴──────┴─────┴─────┴───────┴──────┴──────┘

any

select * from yg any join gz on yg.id=gz.id;     //只返回一个能连上的数据
┌─id─┬─name─┬─age─┬─bid─┬─gz.id─┬───jb─┬───jj─┐
│  1 │ aa   │  23 │   1 │     1 │ 1000 │ 2000 │
│  2 │ bb   │  24 │   2 │     2 │ 2000 │ 1233 │
│  3 │ cc   │  27 │   1 │     3 │ 2000 │ 3000 │
│  4 │ dd   │  13 │   3 │     4 │ 4000 │ 1000 │
│  5 │ ee   │  53 │   3 │     5 │ 5000 │ 2000 │
└────┴──────┴─────┴─────┴───────┴──────┴──────┘

asof

create table emp1(
id Int8,
name String,
ctime DateTime
)engine=Log;

insert into emp1 values
(1,'AA','2021-01-03 00:00:00'),
(1,'AA','2021-01-02 00:00:00'),
(2,'CC','2021-01-01 00:00:00'),
(3,'DD','2021-01-01 00:00:00'),
(4,'EE','2021-01-01 00:00:00');
select * from emp1;
┌─id─┬─name─┬───────────────ctime─┐
│  1 │ AA   │ 2021-01-03 00:00:00 │
│  1 │ AA   │ 2021-01-02 00:00:00 │
│  2 │ CC   │ 2021-01-01 00:00:00 │
│  3 │ DD   │ 2021-01-01 00:00:00 │
│  4 │ EE   │ 2021-01-01 00:00:00 │
└────┴──────┴─────────────────────┘

create table emp2(
id Int8,
name String,
ctime DateTime
)engine=Log;

insert into emp2 values
(1,'aa','2021-01-02 00:00:00'),
(1,'aa','2021-01-02 00:00:00'),
(2,'cc','2021-01-01 00:00:00'),
(3,'dd','2021-01-01 00:00:00');

select * from emp2;
┌─id─┬─name─┬───────────────ctime─┐
│  1 │ aa   │ 2021-01-02 00:00:00 │
│  1 │ aa   │ 2021-01-02 00:00:00 │
│  2 │ cc   │ 2021-01-01 00:00:00 │
│  3 │ dd   │ 2021-01-01 00:00:00 │
└────┴──────┴─────────────────────┘

select * from emp1 all join emp2 on emp1.id=emp2.id;
┌─id─┬─name─┬───────────────ctime─┬─emp2.id─┬─emp2.name─┬──────────emp2.ctime─┐
│  1 │ AA   │ 2021-01-03 00:00:00 │       1 │ aa        │ 2021-01-02 00:00:00 │
│  1 │ AA   │ 2021-01-03 00:00:00 │       1 │ aa        │ 2021-01-02 00:00:00 │
│  1 │ AA   │ 2021-01-02 00:00:00 │       1 │ aa        │ 2021-01-02 00:00:00 │
│  1 │ AA   │ 2021-01-02 00:00:00 │       1 │ aa        │ 2021-01-02 00:00:00 │
│  2 │ CC   │ 2021-01-01 00:00:00 │       2 │ cc        │ 2021-01-01 00:00:00 │
│  3 │ DD   │ 2021-01-01 00:00:00 │       3 │ dd        │ 2021-01-01 00:00:00 │
└────┴──────┴─────────────────────┴─────────┴───────────┴─────────────────────┘

select * from emp1 any join emp2 on emp1.id=emp2.id;
┌─id─┬─name─┬───────────────ctime─┬─emp2.id─┬─emp2.name─┬──────────emp2.ctime─┐
│  1 │ AA   │ 2021-01-03 00:00:00 │       1 │ aa        │ 2021-01-02 00:00:00 │
│  2 │ CC   │ 2021-01-01 00:00:00 │       2 │ cc        │ 2021-01-01 00:00:00 │
│  3 │ DD   │ 2021-01-01 00:00:00 │       3 │ dd        │ 2021-01-01 00:00:00 │
└────┴──────┴─────────────────────┴─────────┴───────────┴─────────────────────┘

select * from emp1 asof join emp2 on emp1.id=emp2.id and emp1.ctime>emp2.ctime;
┌─id─┬─name─┬───────────────ctime─┬─emp2.id─┬─emp2.name─┬──────────emp2.ctime─┐
│  1 │ AA   │ 2021-01-03 00:00:00 │       1 │ aa        │ 2021-01-02 00:00:00 │
└────┴──────┴─────────────────────┴─────────┴───────────┴─────────────────────┘