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);