下面只展示和MySQL或HIVE区别较大的语法
例如ClickHouse建库语法和MySQL几乎1卵样的不予展示
# 创建左表 CREATE TABLE default.temp_left( `a` String COMMENT '匹配键' )ENGINE = MergeTree ORDER BY (a); INSERT INTO default.temp_left SELECT 'A1' AS a UNION ALL SELECT 'A1' AS a UNION ALL SELECT 'A2' AS a UNION ALL SELECT 'A3' AS a; # 创建右表 CREATE TABLE default.temp_right( `a` String COMMENT '匹配键', `b` Nullable(UInt32), `c` UInt32 )ENGINE = MergeTree ORDER BY (a); INSERT INTO default.temp_right SELECT 'A2' AS a,9 AS b,8 AS c UNION ALL SELECT 'A3' AS a,9 AS b,8 AS c UNION ALL SELECT 'A4' AS a,9 AS b,8 AS c; # 左联 SELECT * FROM default.temp_left le LEFT JOIN default.temp_right ri ON le.a=ri.a
左联测试结果
在右表中,b
允许空,a
和c
冇允许空 左联后,联不上的a
是空字符串,联不上的b
是NULL
,联不上的c
是0
INSERT INTO t2 WITH a AS (SELECT * FROM t1) SELECT * FROM a;
和HIVE、MySQL等不一样,ClickHouse的INSERT
写在WITH
之前
CREATE TEMPORARY TABLE temp_t(`a` String,`b` Int32); INSERT INTO temp_t VALUES ('AB',3),('CC',4); SELECT * FROM temp_t; # 结束会话后,临时表不存在
CREATE TEMPORARY TABLE sales( name String COMMENT '产品', city String COMMENT '城市', sale Int32 COMMENT '销量'); INSERT INTO sales VALUES ('椰子','佛山',99),('雪梨','佛山',77),('苹果','佛山',88), ('椰子','广州',80),('雪梨','广州',80),('苹果','广州',70);
SELECT city ,groupArray(name) OVER (PARTITION BY city) FROM sales;
SELECT city, name, sale, rank() OVER(PARTITION BY city ORDER BY sale DESC) FROM sales;
多数情况使用单引号
SELECT "abc"; # 报错 SELECT 'abc'; # 正常查询,返回字符串
CREATE TABLE default.temp_t( `a` String COMMENT "匹配键" )ENGINE=Log; # 字段注释使用双引号报错 CREATE TABLE default.temp_t( `a` String COMMENT '匹配键' )ENGINE=Log; # 正常建表