create table data(a1 float, a2 float);
insert into data values(1::float, 1::float);
do $$
declare
counter integer := 0;
begin
while counter < 50 loop
--SAVE THIS INTO DATA
SELECT a1-0.1*random(),
a2-0.1*random()
from data group by a1, a;
--SAVE THIS INTO DATA (END)
counter := counter 1;
end loop;
end$$;
我想保存的價值a1和a2為資料的第一個元組。（基本上是一個運行計數）
如何將 a 的結果保存SELECT到現有表中？
uj5u.com熱心網友回復：
這會更容易PRIMARY KEY在桌子上：
create table data(id int primary key, a1 float, a2 float);
insert into data values(1, 1, 1);
select * from data;
id | a1 | a2
---- ---- ----
1 | 1 | 1
update data set a1 = a1 - 0.1*random(), a2 = a2 - 0.1*random() where id =1;
select * from data;
id | a1 | a2
---- -------------------- ------------------
1 | 0.9039355377682678 | 0.98859843416964
然后將 合并UPDATE到計數器回圈中：
do $$
declare
counter integer := 0;
begin
while counter < 50 loop
--SAVE THIS INTO DATA
update data set a1 = a1 - 0.1*random(), a2 = a2 - 0.1*random() where id =1;
--SAVE THIS INTO DATA (END)
counter := counter 1;
end loop;
end$$;
select * from data;
id | a1 | a2
---- --------------------- --------------------
1 | -1.3146675853588001 | -1.216530003992667
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/327544.html
標籤：sql PostgreSQL plpgsql
上一篇：識別具有下降趨勢的用戶SQL