腾讯云数据仓库 TCHouse-P select语句-开发指南-文档中心-腾讯云

访问函数
postgres=# select md5(random()::text);
结果：
 md5
----------------------------------
 3eb6c0c8f8355f0b0f0cad7a8f0f7491
数据排序
按某一列排序
postgres=# INSERT into tbase (nickname) VALUES('TBase 好');
postgres=# INSERT into tbase (id,nickname) VALUES(1,'TBase 分布式数据库的时代来了');
postgres=# select * from tbase order by id;
结果：
 id | nickname
----+-----------------------------
 1  | hello TBase
 1  | TBase 分布式数据库的时代来了
 2  | TBase 好
(3 rows)
按第一列排序
postgres=# select * from tbase order by 1;
按 id 升序排序，再按 nickname 降序排序
postgres=# select * from tbase order by id, nickname desc;
随机排序
postgres=# select * from tbase order by random();
计算排序
postgres=# select * from tbase order by md5(nickname);
排序使用子查询
postgres=# select * from tbase order by (select id from tbase order by random() limit 1);
null 值排序结果处理
postgres=# insert into tbase values(4,null);
postgres=# select * from tbase order by nickname nulls first;
postgres=# select * from tbase order by nickname nulls last;
按拼音排序
postgres=# select * from (values ('张三'), ('李四'),('陈五')) t(myname) order by myname;
postgres=# select * from (values ('张三'), ('李四'),('陈五')) t(myname) order by convert(myname::bytea,'UTF-8','GBK');
postgres=# select * from (values ('张三'), ('李四'),('陈五')) t(myname) order by convert_to(myname,'GBK');
postgres=# select * from (values ('张三'), ('李四'),('陈五')) t(myname) order by myname collate "zh_CN.utf8";
where 条件使用
单条件查询
postgres=# select * from tbase where id=1;
多条件 and
postgres=# select * from tbase where id=1 and nickname like '%h%';
多条件 or
postgres=# select * from tbase where id=2 or nickname like '%h%';
ilike 不区分大小写匹配
postgres=# create table t_ilike(id int,mc text);
postgres=# insert into t_ilike values(1,'tbase'),(2,'TBase');
postgres=# select * from t_ilike where mc ilike '%tb%';
where 条件支持子查询
postgres=# select * from tbase where id=(select (random()*2)::integer from tbase order by random() limit 1);
null 值查询方法
postgres=# select * from tbase where nickname is null;
postgres=# select * from tbase where nickname is not null;
exists，只要有记录返回就为真
postgres=# create table t_exists1(id int,mc text);
postgres=# insert into t_exists1 values(1,'tbase'),(2,'TBase');
postgres=# create table t_exists2(id int,mc text);
postgres=# insert into t_exists2 values(1,'tbase'),(1,'TBase');
postgres=# select * from t_exists1 where exists(select 1 from t_exists2 where t_exists1.id=t_exists2.id);
exists 等价写法
postgres=# select t_exists1.* from t_exists1, (select distinct id from t_exists2) as t where t_exists1.id=t.id;
分页查询
默认从第一条开始，返回一条记录：
postgres=# select * from tbase limit 1;
结果：
 id | nickname
----+-------------
 1 | hello TBase
(1 row)
使用 offset 指定从第几条开始，0 表示第一条开始，返回 1 条记录：
postgres=# select * from tbase limit 1 offset 0;
结果：
 id | nickname
----+-------------
 1 | hello TBase
(1 row)
从第 3 条开始，返回两条记录：
postgres=# select * from tbase limit 1 offset 2;
结果：
 id | nickname
----+-----------------
 1 | TBase 分布式数据库的时代来了
(1 row)
使用 order by 可以获得一个有序的结果：
postgres=# select * from tbase order by id limit 1 offset 2;
结果：
 id | nickname
----+-----------
 2 | TBase 好
(1 row)
合并多个查询结果
不过滤重复的记录：
postgres=# select * from tbase union all select * from t_appoint_col;
结果：
 id | nickname
----+-----------------
 1 | hello TBase
 2 | TBase 好
 1 | TBase 分布式数据库的时代来了
 1 | hello TBase
(4 rows)
过滤重复的记录：
postgres=# select * from tbase union select * from t_appoint_col;
结果：
 id | nickname
----+-----------------
 1 | TBase 分布式数据库的时代来了
 1 | hello TBase
 2 | TBase 好
(3 rows)
返回两个结果的交集
postgres=# select * from t_intersect1 INTERSECT select * from t_intersect2;
结果：
 id | mc
----+-------
 1 | tbase
(1 row)
返回两个结果的差集
postgres=# select * from t_except1 except select * from t_except2;
结果：
 id | mc
----+-------
 2 | tbase
(1 row)
any 用法
只需要大于其中一个值即为真：
postgres=# select * from t_any where id > any (select 1 union select 3);
结果：
 id | mc
----+-------
 2 | TBase
(1 row)
all 用法
需要大于所有值才为真：
postgres=# select * from t_all where id > all (select 1 union select 2);
结果：
 id | mc
----+-------
 3 | TBase
(1 row)
聚集查询
统计记录数
postgres=# select count(1) from tbase;
结果：
 count
-------
 3
(1 row)
统计不重复值的记录表
postgres=# select count(distinct id) from tbase;
结果：
 count
-------
 2
(1 row)
求和
postgres=# select sum(id) from tbase;
结果：
 sum
-----
 4
(1 row)
求最大值
postgres=# select max(id) from tbase;
结果：
 max
-----
 2
(1 row)
求最小值
postgres=# select min(id) from tbase;
结果：
 min
-----
 1
(1 row)
求平均值
postgres=# select avg(id) from tbase;
结果：
        avg
--------------------
 1.3333333333333333
(1 row)
分组字段合并成一个字符串
postgres=# create table t1(f1 int, f2 text, f3 text);
postgres=# insert into t1 values(1,'a','a');
postgres=# insert into t1 values(1,'b','b');
postgres=# insert into t1 values(2,'a','a');
postgres=# select f1, string_agg(f2, ',') from t1 group by f1;
结果：
 f1 | string_agg
----+------------
 1  | a,b
 2  | a
(2 rows)
去重和自定义聚合函数
在聚合函数中使用 DISTINCT 和 ORDER BY 需要满足特定条件，可以自定义函数来实现更灵活的聚合。
多表关联
内连接
postgres=# select * from tbase inner join t_appoint_col on tbase.id=t_appoint_col.id;
结果：
 id | nickname | id | nickname
----+-----------------------------+----+-------------
 1  | hello TBase               | 1  | hello TBase
 1  | TBase 分布式数据库的时代来了 | 1  | hello TBase
(2 rows)
左外连接
postgres=# select * from tbase left join t_appoint_col on tbase.id=t_appoint_col.id;
结果：
 id | nickname   | id | nickname
----+------------+----+-------------
 1  | hello TBase | 1  | hello TBase
 2  | TBase 好    |     |
 1  | TBase 分布式数据库的时代来了 | 1  | hello TBase
(3 rows)
右外连接
postgres=# select * from tbase right join t_appoint_col on tbase.id=t_appoint_col.id;
结果：
 id | nickname                     | id | nickname
----+-------------------------------+----+-------------
 1  | TBase 分布式数据库的时代来了 | 1  | hello TBase
 1  | hello TBase                  | 1  | hello TBase
    |                               5  | Power TBase
(3 rows)
全连接
postgres=# select * from tbase full join t_appoint_col on tbase.id=t_appoint_col.id;
结果：
 id | nickname   | id | nickname
----+------------+----+-------------
 1  | hello TBase | 1  | hello TBase
 2  | TBase 好    |     |
 1  | TBase 分布式数据库的时代来了 | 1  | hello TBase
    |              | 5  | Power TBase
(4 rows)
聚合函数并发计算
单核计算
postgres=# set max_parallel_workers_per_gather to 0;
postgres=# select count(1) from t_count;
二核并行
postgres=# set max_parallel_workers_per_gather to 2;
postgres=# select count(1) from t_count;
四核并行
postgres=# set max_parallel_workers_per_gather to 4;
postgres=# select count(1) from t_count;
not in 子句包含 null 的情况
postgres=# create table t_not_in(id int, mc text);
NOTICE: Replica identity is needed for shard table, please add to this table through "alter table" command.
CREATE TABLE
postgres=# insert into t_not_in values(1, 'tbase'), (2, 'pgxz');
INSERT 0 2
postgres=# select * from t_not_in where id not in (3, 5);
结果：
 id | mc
----+-------
 1 | tbase
 2 | pgxz
(2 rows)
postgres=# select * from t_not_in where id not in (3, 5, null);
结果：无输出行
﻿
只查询特定数据节点（dn）的数据
postgres=# create table t_direct(id int, mc text);
NOTICE: Replica identity is needed for shard table, please add to this table through "alter table" command.
CREATE TABLE
postgres=# insert into t_direct values(1, 'tbase'), (3, 'pgxz');
INSERT 0 2
postgres=# EXECUTE DIRECT ON (dn001) 'select * from t_direct';
结果：
 id | mc
----+-------
 1 | tbase
(1 row)
postgres=# EXECUTE DIRECT ON (dn002) 'select * from t_direct';
结果：
 id | mc
----+------
 3 | pgxz
(1 row)
查询所有节点的数据：
postgres=# select * from t_direct;
结果：
 id | mc
----+-------
 1 | tbase
 3 | pgxz
(2 rows)
特殊应用
多行变成单行
postgres=# create table t_mulcol_tosimplecol(id int, mc text);
NOTICE: Replica identity is needed for shard table, please add to this table through "alter table" command.
CREATE TABLE
postgres=# insert into t_mulcol_tosimplecol values(1, 'tbase'), (2, 'TBase');
INSERT 0 2
postgres=# select array_to_string(array(select mc from t_mulcol_tosimplecol), ',');
结果：
 array_to_string
-----------------
 tbase,TBase
(1 row)
一列变成多行
postgres=# create table t_col_to_mulrow(id int, mc text);
NOTICE: Replica identity is needed for shard table, please add to this table through "alter table" command.
CREATE TABLE
postgres=# insert into t_col_to_mulrow values(1, 'tbase,TBase');
INSERT 0 1
postgres=# select regexp_split_to_table((select mc from t_col_to_mulrow where id=1 limit 1), ',');
结果：
 regexp_split_to_table
-----------------------
 tbase
 TBase
(2 rows)
查询记录所在数据节点
postgres=# select xc_node_id, * from t1;
结果：
 xc_node_id | f1 | f2
------------+----+----
 2142761564 | 1  | 3
 2142761564 | 1  | 3
(2 rows)
查询并映射节点名称：
postgres=# select t1.xc_node_id, pgxc_node.node_name, t1.* from t1, pgxc_node where t1.xc_node_id=pgxc_node.node_id;
结果：
 xc_node_id | node_name | f1 | f2
------------+-----------+----+----
 2142761564 | dn001     | 1  | 3
 2142761564 | dn001     | 1  | 3
(2 rows)
grouping sets/rollup/cube 用法
group by 用法
创建销售明细表并插入数据：
create table t_grouping(id int, dep varchar(20), product varchar(20), num int);
insert into t_grouping values(1, '业务 1 部', '手机', 90);
-- 更多插入数据 ...
按部门和产品进行分组汇总：
postgres=# select dep, product, sum(num) from t_grouping group by dep, product order by dep, product;
结果：
 dep       | product   | sum
-----------+-----------+-----
 业务 1 部 | 电脑       | 80
 业务 1 部 | 手机       | 160
 业务 2 部 | 电脑       | 120
 业务 2 部 | 手机       | 50
 业务 3 部 | 电脑       | 80
 业务 3 部 | 手机       | 160
使用 grouping sets 进行分组：
postgres=# select dep, product, sum(num) from t_grouping group by grouping sets((dep), (product), ());
结果：
 dep       | product   | sum
-----------+-----------+-----
 业务 1 部 | (空)      | 240
 业务 2 部 | (空)      | 170
 业务 3 部 | (空)      | 240
 电脑      | (空)      | 280
 手机      | (空)      | 370
(空)       | (空)      | 650
使用 rollup 和 cube：
postgres=# select dep, product, sum(num) from t_grouping group by rollup((dep), (product));
postgres=# select dep, product, sum(num) from t_grouping group by cube((dep), (product));
结果与 grouping sets 相同。
﻿
PREPARE 预备使用
创建一个预备语句
postgres=# create table t1(f1 int, f2 int);
CREATE TABLE
postgres=# insert into t1 values(1, 1), (2, 2);
COPY 2
postgres=# PREPARE usrrptplan (int) AS SELECT * FROM t1 WHERE f1=$1;
PREPARE
postgres=# EXECUTE usrrptplan(1);
结果：
 f1 | f2
----+----
 1  | 1
(1 row)
释放一个预备语句
postgres=# DEALLOCATE usrrptplan;
DEALLOCATE
postgres=# EXECUTE usrrptplan(1);
ERROR: prepared statement "usrrptplan" does not exist
﻿
select语句

本页目录：

访问函数

数据排序

按某一列排序

按第一列排序

按 id 升序排序，再按 nickname 降序排序

随机排序

计算排序

排序使用子查询

null 值排序结果处理

按拼音排序

where 条件使用

单条件查询

多条件 and

多条件 or

ilike 不区分大小写匹配

where 条件支持子查询

null 值查询方法

exists，只要有记录返回就为真

exists 等价写法

分页查询

合并多个查询结果

返回两个结果的交集

返回两个结果的差集

any 用法

all 用法

聚集查询

统计记录数

统计不重复值的记录表

求和

求最大值

求最小值

求平均值

分组字段合并成一个字符串

去重和自定义聚合函数

多表关联

内连接

左外连接

右外连接

全连接

聚合函数并发计算

单核计算

二核并行

四核并行

not in 子句包含 null 的情况

只查询特定数据节点（dn）的数据

特殊应用

多行变成单行

一列变成多行

查询记录所在数据节点

grouping sets/rollup/cube 用法

PREPARE 预备使用

创建一个预备语句

释放一个预备语句
