TDSQL PostgreSQL版 SELECT 语法

二、数据排序

·按某一列排序

postgres=# INSERT into tdsql_pg (nickname) VALUES('tdsql_pg好'); INSERT 0 1postgres=# INSERT into tdsql_pg (id,nickname) VALUES(1,' TDSQL PG版分布式数据库的时代来了'); INSERT 0 1postgres=# select * from tdsql_pg order by id; id | nickname —-+—————————– 1 | hello tdsql_pg 1 | tdsql_pg分布式数据库的时代来了 2 | tdsql_pg好(3 rows)

·按第一列排序

postgres=# select * from tdsql_pg order by 1; id | nickname —-+—————————– 1 | hello tdsql_pg 1 | tdsql_pg分布式数据库的时代来了 2 | tdsql_pg好(3 rows)

·按 ID 升级排序，再按 nickname 降序排序

postgres=# select * from tdsql_pg order by id,nickname desc; id | nickname —-+—————————– 1 | tdsql_pg分布式数据库的时代来了 1 | hello tdsql_pg 2 | tdsql_pg好(3 rows)

效果与上面的语句一样。

postgres=# select * from tdsql_pg order by 1,2 desc; id | nickname —-+—————————– 1 | tdsql_pg分布式数据库的时代来了 1 | hello tdsql_pg 2 | tdsql_pg好(3 rows)

·随机排序

postgres=# select * from tdsql_pg order by random(); id | nickname —-+—————————– 1 | tdsql_pg分布式数据库的时代来了 2 | tdsql_pg好 1 | hello tdsql_pg(3 rows)

·计算排序

postgres=# select * from tdsql_pg order by md5(nickname); id | nickname —-+—————————– 2 | tdsql_pg好 1 | tdsql_pg分布式数据库的时代来了 1 | hello tdsql_pg(3 rows)

排序也能用子查询。

postgres=# select * from tdsql_pg order by (select id from tdsql_pg order by random() limit 1); id | nickname —-+—————————– 1 | hello tdsql_pg 2 | tdsql_pg好 1 | tdsql_pg分布式数据库的时代来了(3 rows)

·null 值排序结果处理

postgres=# insert into tdsql_pg values(4,null);INSERT 0 1

null 值记录排在最前面。

postgres=# select * from tdsql_pg order by nickname nulls first; id | nickname —-+—————————– 4 | 1 | hello tdsql_pg 1 | tdsql_pg分布式数据库的时代来了 2 | tdsql_pg好(4 rows)

null 值记录排在最后。

postgres=# select * from tdsql_pg order by nickname nulls last; id | nickname —-+—————————– 1 | hello tdsql_pg 1 | tdsql_pg分布式数据库的时代来了 2 | tdsql_pg好 4 | (4 rows)

·按拼音排序

postgres=# select * from (values ('张三'), ('李四'),('陈五')) t(myname) order by myname; myname ——– 张三 李四 陈五(3 rows)

如果不加处理，则按汉字的 UTF-8 编码进行排序，不符合中国人使用习惯。

·使用 convert 函数实现汉字按拼音进行排序。

postgres=# select * from (values ('张三'), ('李四'),('陈五')) t(myname) order by convert(myname::bytea,'UTF-8','GBK'); myname ——– 陈五 李四 张三(3 rows)

·使用 convert_to 函数实现汉字按拼音进行排序。

postgres=# select * from (values ('张三'), ('李四'),('陈五')) t(myname) order by convert_to(myname,'GBK'); myname ——– 陈五 李四 张三(3 rows)

·通过指定排序规则 collact 来实现汉字按拼音进行排序。

postgres=# select * from (values ('张三'), ('李四'),('陈五')) t(myname) order by myname collate "zh_CN.utf8"; myname \\——– 陈五 李四 张三(3 rows)

三、where 条件使用

·单条件查询

postgres=# select * from tdsql_pg where id=1; id | nickname —-+—————————– 1 | hello tdsql_pg 1 | tdsql_pg分布式数据库的时代来了

·多条件 and

postgres=# select * from tdsql_pg where id=1 and nickname like '%h%'; id | nickname —-+————- 1 | hello tdsql_pg(1 row)

·多条件 or

postgres=# select * from tdsql_pg where id=2 or nickname like '%h%'; id | nickname —-+————- 1 | hello tdsql_pg 2 | tdsql_pg好(2 rows)

·ilike 不区分大小写匹配

postgres=# create table t_ilike(id int,mc text);NOTICE: Replica identity is needed for shard table, please add to this table through "alter table" command.CREATE TABLEpostgres=# insert into t_ilike values(1,'tdsql_pg'),(2,'tdsql_pg');INSERT 0 2postgres=# select * from t_ilike where mc ilike '%td%'; id | mc —-+——- 1 | tdsql_pg 2 | tdsql_pg(2 rows)

·where 条件也能支持子查询

postgres=# select * from tdsql_pg where id=(select (random()*2)::integer from tdsql_pg order by random() limit 1); id | nickname —-+———-(0 rows) postgres=# select * from tdsql_pg where id=(select (random()*2)::integer from tdsql_pg order by random() limit 1); id | nickname —-+—————————– 1 | hello tdsql_pg 1 | tdsql_pg分布式数据库的时代来了(2 rows)

·null 值查询方法

postgres=# select * from tdsql_pg where nickname is null; id | nickname —-+———- 4 | (1 row) postgres=# select * from tdsql_pg where nickname is not null; id | nickname —-+—————————– 1 | hello tdsql_pg 2 | tdsql_pg好 1 | tdsql_pg分布式数据库的时代来了(3 rows)

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

postgres=# create table t_exists1(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_exists1 values(1,'tdsql_pg'),(2,'tdsql_pg'); INSERT 0 2 postgres=# create table t_exists2(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_exists2 values(1,'tdsql_pg'),(1,'tdsql_pg');INSERT 0 2 postgres=# select * from t_exists1 where exists(select 1 from t_exists2 where t_exists1.id=t_exists2.id) ; id | mc —-+——- 1 | tdsql_pg(1 row)

·exists 等价写法

postgres=# select t_exists1.* from t_exists1,(select distinct id from t_exists2) as t where t_exists1.id=t.id;; id | mc —-+——- 1 | tdsql_pg(1 row)

四、分页查询

默认从第一条开始，返回一条记录。

postgres=# select * from tdsql_pg limit 1; id | nickname —-+————- 1 | hello tdsql_pg(1 row)

使用 offset 指定从第几条开始，0表示第一条开始，返回1条记录。

postgres=# select * from tdsql_pg limit 1 offset 0; id | nickname —-+————- 1 | hello tdsql_pg(1 row)

从第3条开始，返回二条记录。

postgres=# select * from tdsql_pg limit 1 offset 2; id | nickname —-+—————————– 1 | tdsql_pg分布式数据库的时代来了(1 row)

上面的语句没有使用排序，返回结果不可预知，使用 order by 可以获得一个有序的结果。

postgres=# select * from tdsql_pg order by id limit 1 offset 2; id | nickname —-+———– 2 | tdsql_pg好(1 row)

五、合并多个查询结果

·不过滤重复的记录。

postgres=# select * from tdsql_pg union all select * from t_appoint_col; id | nickname —-+—————————– 1 | hello tdsql_pg 2 | tdsql_pg好 1 | tdsql_pg分布式数据库的时代来了 1 | hello tdsql_pg(4 rows)

·过滤重复的记录。

postgres=# select * from tdsql_pg union select * from t_appoint_col; id | nickname —-+—————————– 1 | tdsql_pg分布式数据库的时代来了 1 | hello tdsql_pg 2 | tdsql_pg好(3 rows)

·每个子查询分布在合并结果中的使用。

postgres=# select * from ( select * from tdsql_pg limit 1) as t union all select * from (select * from t_appoint_col limit 1) as t ; id | nickname —-+————- 1 | hello tdsql_pg 1 | hello tdsql_pg(2 rows)

六、返回两个结果的交集

postgres=# create table t_intersect1(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_intersect1 values(1,'tdsql_pg'),(2,'tdsql_pg');INSERT 0 2 postgres=# create table t_intersect2(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_intersect2 values(1,'tdsql_pg'),(3,'tdsql_pg');INSERT 0 2 postgres=# select * from t_intersect1 INTERSECT select * from t_intersect2; id | mc —-+——- 1 | tdsql_pg(1 row)

七、返回两个结果的差集

postgres=# create table t_except1(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_except1 values(1,'tdsql_pg'),(2,'tdsql_pg');INSERT 0 2 postgres=# create table t_except2(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_except2 values(1,'tdsql_pg'),(3,'tdsql_pg');INSERT 0 2 postgres=# select * from t_except1 except select * from t_except2; id | mc —-+——- 2 | tdsql_pg(1 row)

八、any 用法

只需要大于其中一个值即为真。

postgres=# create table t_any(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_any values(1,'tdsql_pg'),(2,'tdsql_pg'); INSERT 0 2 postgres=# select * from t_any where id>any (select 1 union select 3); id | mc —-+——- 2 | tdsql_pg(1 row)

九、all 用法

需要大于所有值才为真。

postgres=# create table t_all(id int,mc text);NOTICE: Replica identity is needed for shard table, please add to this table through "alter table" command.CREATE TABLEpostgres=# insert into t_all values(2,'tdsql_pg'),(3,'tdsql_pg'); INSERT 0 2 postgres=# select * from t_all where id>all (select 1 union select 2); id | mc —-+——- 3 | tdsql_pg(1 row)

十、聚集查询

·统计记录数。

postgres=# select count(1) from tdsql_pg; count ——- 3(1 row)

·统计不重复值的记录表。

postgres=# select count(distinct id) from tdsql_pg; count ——- 2(1 row)

·求和。

postgres=# select sum(id) from tdsql_pg; sum —– 4(1 row)

·求最大值。

postgres=# select max(id) from tdsql_pg; max —– 2(1 row)

·求最小值。

postgres=# select min(id) from tdsql_pg; min —– 1(1 row)

·求平均值。

postgres=# select avg(id) from tdsql_pg; avg ——————– 1.3333333333333333(1 row)

十一、多表关联

·内连接

postgres=# select * from tdsql_pg inner join t_appoint_col on tdsql_pg.id=t_appoint_col.id; id | nickname | id | nickname —-+—————————–+—-+————- 1 | hello tdsql_pg | 1 | hello tdsql_pg 1 | tdsql_pg分布式数据库的时代来了 | 1 | hello tdsql_pg(2 rows)

·左外连接

postgres=# select * from tdsql_pg left join t_appoint_col on tdsql_pg.id=t_appoint_col.id; id | nickname | id | nickname —-+—————————–+—-+————- 1 | hello tdsql_pg | 1 | hello tdsql_pg 2 | tdsql_pg好 | | 1 | tdsql_pg分布式数据库的时代来了 | 1 | hello tdsql_pg(3 rows)

·右外连接

postgres=# select * from tdsql_pg right join t_appoint_col on tdsql_pg.id=t_appoint_col.id; id | nickname | id | nickname —-+—————————–+—-+————-1 | tdsql_pg分布式数据库的时代来了 | 1 | hello tdsql_pg1 | hello tdsql_pg | 1 | hello tdsql_pg | | 5 | Power tdsql_pg(3 rows)

·全连接

postgres=# select * from tdsql_pg full join t_appoint_col on tdsql_pg.id=t_appoint_col.id; id | nickname | id | nickname —-+—————————–+—-+————-1 | hello tdsql_pg | 1 | hello tdsql_pg2 | tdsql_pg好 | | 1 | tdsql_pg分布式数据库的时代来了 | 1 | hello tdsql_pg | | 5 | Power tdsql_pg(4 rows)

十二、聚合函数并发计算

·单核计算

postgres=# \\timing Timing is on.postgres=# set max_parallel_workers_per_gather to 0;SETTime: 0.633 mspostgres=# select count(1) from t_count;count ———-20000000(1 row)Time: 3777.518 ms (00:03.778)

·二核并行

postgres=# set max_parallel_workers_per_gather to 2;SETTime: 0.478 mspostgres=# select count(1) from t_count; count ———- 20000000(1 row) Time: 2166.481 ms (00:02.166)

·四核并行

postgres=# set max_parallel_workers_per_gather to 4;SETTime: 0.315 mspostgres=# select count(1) from t_count; count ———-20000000(1 row)Time: 1162.433 ms (00:01.162)postgres=#

十三、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 TABLEpostgres=# insert into t_not_in values(1,'tdsql_pg'),(2,'pgxz');INSERT 0 2postgres=# select * from t_not_in where id not in (3,5); id | mc —-+——- 1 | tdsql_pg 2 | pgxz(2 rows) postgres=# select * from t_not_in where id not in (3,5,null); id | mc —-+—-(0 rows)

十四、只查某个 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 TABLEpostgres=# insert into t_direct values(1,'tdsql_pg'),(3,'pgxz');INSERT 0 2postgres=# EXECUTE DIRECT ON (dn001) 'select * from t_direct'; id | mc —-+——- 1 | tdsql_pg(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 | tdsql_pg 3 | pgxz(2 rows) postgres=#

十五、特殊应用

·多行变成单行

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 TABLEpostgres=# insert into t_mulcol_tosimplecol values(1,'tdsql_pg'),(2,'tdsql_pg'); INSERT 0 2postgres=# select array_to_string(array(select mc from t_mulcol_tosimplecol),',');array_to_string —————–tdsql_pg,tdsql_pg(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 TABLEpostgres=# insert into t_col_to_mulrow values(1,'tdsql_pg,tdsql_pg'); INSERT 0 1postgres=# select regexp_split_to_table((select mc from t_col_to_mulrow where id=1 limit 1), ',');regexp_split_to_table ———————–tdsql_pgtdsql_pg(2 rows)

十六、查询记录所在 DN

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) postgres=#

十八、group by 用法

·销售明细表。

create table t_grouping(id int,dep varchar(20),product varchar(20),num int);insert into t_grouping values(1,'业务1部','手机',90);insert into t_grouping values(2,'业务1部','电脑',80);insert into t_grouping values(3,'业务1部','手机',70);insert into t_grouping values(4,'业务2部','电脑',60);insert into t_grouping values(5,'业务2部','手机',50);insert into t_grouping values(6,'业务2部','电脑',60);insert into t_grouping values(7,'业务3部','手机',70);insert into t_grouping values(8,'业务3部','电脑',80);insert into t_grouping values(9,'业务3部','手机',90);

·按 dep、product 两级汇总分数。

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

注意：说明：

注意：grouping sets 的每个子列表可以指定零个或多个列或表达式，并且与其直接在 GROUP BY 子句中的解释方式相同。 一个空的分组集合意味着所有的行都被聚合到一个组中。

如按 name、class 单级分别汇总，再计算一个总分。

postgres=# select dep,product,sum(num) from t_grouping group by grouping sets((dep),(product),()) order by dep,product; dep | product | sum ———+———+—– 业务1部 | | 240 业务2部 | | 170 业务3部 | | 240 | 电脑 | 280 | 手机 | 370 | | 650

使用 grouping sets 代替 group by 。

postgres=# select dep,product,sum(num) from t_grouping group by grouping sets((dep,product)) order by dep,product; dep | product | sum ———+———+—– 业务1部 | 电脑 | 80 业务1部 | 手机 | 160 业务2部 | 电脑 | 120 业务2部 | 手机 | 50 业务3部 | 电脑 | 80 业务3部 | 手机 | 160

20、使用 rollup

rollup((a),(b)) 等价于 grouping sets((a,b),(a),())。

postgres=# select dep,product,sum(num) from t_grouping group by rollup((dep),(product)) order by dep,product; dep | product | sum ———+———+—– 业务1部 | 电脑 | 80 业务1部 | 手机 | 160 业务1部 | | 240 业务2部 | 电脑 | 120 业务2部 | 手机 | 50 业务2部 | | 170 业务3部 | 电脑 | 80 业务3部 | 手机 | 160 业务3部 | | 240 | | 650

该功能等价于 grouping sets((dep, product),( dep),())。

postgres=# select dep,product,sum(num) from t_grouping group by grouping sets((dep, product),( dep),()) order by dep,product; dep | product | sum ———+———+—– 业务1部 | 电脑 | 80 业务1部 | 手机 | 160 业务1部 | | 240 业务2部 | 电脑 | 120 业务2部 | 手机 | 50 业务2部 | | 170 业务3部 | 电脑 | 80 业务3部 | 手机 | 160 业务3部 | | 240 | | 650

21、使用 cube

cube((a),(b)) 等价于 grouping sets((a,b),(a),(b),()) 。

postgres=# select dep,product,sum(num) from t_grouping group by cube((dep),(product)) order by dep,product; dep | product | sum ———+———+—– 业务1部 | 电脑 | 80 业务1部 | 手机 | 160 业务1部 | | 240 业务2部 | 电脑 | 120 业务2部 | 手机 | 50 业务2部 | | 170 业务3部 | 电脑 | 80 业务3部 | 手机 | 160 业务3部 | | 240 | 电脑 | 280 | 手机 | 370 | | 650

该功能等价于 grouping sets((name,class),(name),(class),())。

postgres=# select dep,product,sum(num) from t_grouping group by grouping sets((dep,product),(dep),(product),()) order by dep,product; dep | product | sum ———+———+—– 业务1部 | 电脑 | 80 业务1部 | 手机 | 160 业务1部 | | 240 业务2部 | 电脑 | 120 业务2部 | 手机 | 50 业务2部 | | 170 业务3部 | 电脑 | 80 业务3部 | 手机 | 160 业务3部 | | 240 | 电脑 | 280 | 手机 | 370 | | 650