我使用此 SQL 查詢為表生成測驗資料onboarding_tasks：
INSERT into onboarding_tasks (business_name, meta_title, status, task_type)
SELECT
'Business name ' || id AS business_name,
left (md5(random()::text), 10) AS meta_title,
(ARRAY['NEW','IN_PROGRESS','COMPLETED'])[floor(random()*3) 1] AS status,
(ARRAY['CHECK', 'TEST'])[floor(random()*3) 1] AS task_type
FROM generate_series(1,25) as g(id);
在第二個查詢中，我使用id第一個表生成測驗資料onboarding_tasks：
INSERT into onboarding_task_item (title, task_id, onboarding_tasks)
SELECT
left (md5(random()::text), 10) AS title,
ot.id,
generate_series(1,50) AS onboarding_tasks
FROM onboarding_tasks ot;
演示
現在我在 table 中只生成 1 行onboarding_task_item。如何生成 4 個表格行，每行包含測驗資料onboarding_tasks？
uj5u.com熱心網友回復：
只是CROSS JOIN帶有geneate_series()呼叫的結果集，這意味著結果集的所有記錄都將與來自 的所有記錄連接generate_series()，例如
SELECT
left (md5(random()::text), 10) AS title,
ot.id,
generate_series(1,5) AS onboarding_tasks
FROM onboarding_tasks ot
CROSS JOIN generate_series(1,4);
或者，如果您需要生成的值，可能像這樣：
SELECT
left (md5(random()::text), 10) AS title,
ot.id,
j.id AS gen_id
FROM onboarding_tasks ot
CROSS JOIN generate_series(1,4) j(id);
演示： db<>fiddle
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/348642.html
標籤：sql PostgreSQL
上一篇：Postgres獲取當前模式名稱