clickhouse常用语句汇总——持续更新中 - 实践

一、查询判断是否包含指定列

1.根据数据库，表名查看表包含的列

SELECT name
FROM system.
columns
WHERE
table = 'table_name'
AND
database = 'databasename'
;

2.查找指定列target_column是否是表table_name的列

SELECT count(
) >
0
AS column_exists
FROM system.
columns
WHERE
table = 'table_name'
AND
database = 'databasename'
AND name = 'target_column'
;

二、一次查看多表条数count值

select
(
select count(*
)
as tablename1 from databasename.tablename1)
,
(
select count(*
)
as tablename2 from databasename.tablename2)
,
(
select count(*
)
as tablename3 from databasename.tablename3)

三、OR EXISTS (SELECT 1 FROM …) 判断条件中的两个条件之一是否成立

1.基本语法：

SELECT ...
FROM ...
WHERE condition1
OR
EXISTS (
SELECT 1
FROM table_name WHERE subquery_condition)
;

2.使用示例：
示例1：判断两表join后是否有数据

or
exists
(
SELECT 1
FROM databasename.tablename1 T1 join databasename.tablename2 as T2
on T1.id=T2.id AND T1.num=T2.num AND T1.score=T2.score)
)

示例2： 查询有订单的用户，或年龄大于 30 的用户

SELECT *
FROM users
WHERE age >
30
OR
EXISTS (
SELECT 1
FROM orders WHERE orders.user_id = users.id)
;

示例3：NOT EXISTS配合使用

SELECT *
FROM users
WHERE NOT
EXISTS (
SELECT 1
FROM orders WHERE orders.user_id = users.id)
OR
EXISTS (
SELECT 1
FROM orders WHERE orders.user_id = users.id AND amount >
1000
)
;