all用法

• 复制
  复制成功

postgres=# create table t_all(id int,mc text);
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)

• 复制
  复制成功

drop table if exists t_all_dml_20220718_1;

create table t_all_dml_20220718_1(v int, w int);

insert into t_all_dml_20220718_1 values(1, 1);

insert into t_all_dml_20220718_1 values(2, 2);

update t_all_dml_20220718_1 set w = 3 where w > all(0, 1);

delete t_all_dml_20220718_1 where w > all(0, 1);

select * from t_all_dml_20220718_1 order by v;

insert into t_all_dml_20220718_1 select 2, 2 from t_all_dml_20220718_1 where w >= all(0, 1);

with cte_all_update as (select v from t_all_dml_20220718_1 where v > all(0, 1)) update t_all_dml_20220718_1 set w = (select v from cte_all_update order by v limit 1) where w > all(0, 1);

select * from t_all_dml_20220718_1 order by v;

with cte_all_delete as (select v from t_all_dml_20220718_1 where v > all(0, 1)) delete t_all_dml_20220718_1 where w = all(select v from cte_all_delete order by v limit 1);

select * from t_all_dml_20220718_1 order by v;

with cte_all_insert as (select v, w from t_all_dml_20220718_1 where v >= all(0, 1)) insert into t_all_dml_20220718_1 select v, w from cte_all_insert;

select * from t_all_dml_20220718_1 order by v;