TDSQL PostgreSQL版 SELECT 语法-开发指南-文档中心-腾讯云

访问函数
postgres=# select md5(random()::text);
        md5         
----------------------------------
 3eb6c0c8f8355f0b0f0cad7a8f0f7491
数据排序
按某一列排序
postgres=# INSERT into tdsql_pg (nickname) VALUES('tdsql_pg好');               
INSERT 0 1
postgres=# INSERT into tdsql_pg (id,nickname) VALUES(1,' TDSQL PG版分布式数据库的时代来了');        
INSERT 0 1
postgres=# 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 TABLE
postgres=# insert into t_ilike values(1,'tdsql_pg'),(2,'tdsql_pg');
INSERT 0 2
postgres=# 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 TABLE
postgres=# 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_pg
1 | 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_pg
2 | 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;
SET
Time: 0.633 ms
postgres=# 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;
SET
Time: 0.478 ms
postgres=# 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;
SET
Time: 0.315 ms
postgres=# 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 TABLE
postgres=# insert into t_not_in values(1,'tdsql_pg'),(2,'pgxz');
INSERT 0 2
postgres=# 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 TABLE
postgres=# insert into t_direct values(1,'tdsql_pg'),(3,'pgxz');
INSERT 0 2
postgres=# 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 TABLE
﻿
postgres=# insert into t_mulcol_tosimplecol values(1,'tdsql_pg'),(2,'tdsql_pg');  
INSERT 0 2
﻿
postgres=# 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 TABLE
﻿
postgres=# insert into t_col_to_mulrow values(1,'tdsql_pg,tdsql_pg');  
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 
-----------------------
tdsql_pg
tdsql_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=# 
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);
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
使用 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
使用 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
﻿
SELECT 语法

本页目录：

访问函数

数据排序

where 条件使用

分页查询

合并多个查询结果

返回两个结果的交集

返回两个结果的差集

any 用法

all 用法

聚集查询

多表关联

聚合函数并发计算

not in 中包含了null，结果全为真

只查某个 DN 的数据

特殊应用

查询记录所在 DN

grouping sets/rollup/cube 用法

group by 用法

使用 grouping sets

使用 rollup

使用 cube
