我正在苦苦掙扎如何使用 .sql 腳本遷移資料 我對 SQL 還是很陌生，并試圖弄清楚如何完全在 .SQL 上遷移資料。我想將舊資料作為具有不同結構的新記錄添加到新表中
這是我的情況：我有兩個舊的 tbl，我想將它與我的新結構化 tbl 合并到一個額外的列。我有點卡在這里，因為我不習慣在 .SQL 上使用條件
表的前綴是模式
舊表
舊組
| ID | 組的名字 |
|---|---|
| 10 | 頂尖 |
| 11 | 預測 |
| 12 | 托爾 |
old.sub_groups
| parent_id | 子組 |
|---|---|
| 10 | 亞頂點 |
| 11 | 子預 |
| 11 | 子子pre |
新表：預期的遷移資料
public.new_groups *id 自動遞增
新鮮的新填充表
| ID | 組的名字 | 等級 | parent_id |
|---|---|---|---|
| 0 | 頂尖 | 1 | 10 |
| 1 | 預測 | 1 | 11 |
| 2 | 托爾 | 空值 | 空值 |
| 3 | 亞頂點 | 2 | 10 |
| 4 | 子預 | 2 | 11 |
| 5 | 子子pre | 2 | 11 |
我想將它與條件合并。但我跟不上 SQL 查詢
Condition 1: If old.groups.id doesn't detect any match on old.sub_groups.parent_id it will be inserted to public.new_groups but the public.new_groups.level and public.new_groups.parent_id will be default to null.
Condition 2: If old.groups.id detects a match on old.sub_groups.parent_id it will be also inserted to public.new_groups then tag the level as 1 (1 means parent group in my structure) but with another new three inserted records which is the sub_groups it detected refer to tbl.new_groups id [3, 4, and 5] and tag the level as 2. and the parent_id will be the parent_id of the old.sub_groups or the id of the parent in old.groups
This is my unfinished Query im only able to call the data its missing out the conditional and the update but i think this is also wrong:
INSERT INTO public.new_groups(
SELECT *, b.sub_group as group_name, b.parent_id FROM old.groups as a
LEFT JOIN old.sub_groups as b ON a.id = b.parent_id....
)
uj5u.com熱心網友回復：
當您像這樣創建表時：
CREATE TABLE new (
id SERIAL PRIMARY KEY ,
group_name VARCHAR(20),
level INTEGER,
parent_id INTEGER
);
您可以使用以下陳述句復制表：
INSERT INTO new(group_name, level, parent_id)
SELECT DISTINCT
group_name,
CASE WHEN subgroups.parent_id IS NULL THEN NULL ELSE 1 END as level,
subgroups.parent_id
FROM old
LEFT JOIN subgroups ON old.id = subgroups.parent_id
UNION ALL
SELECT
sub_group,
2,
parent_id
FROM subgroups;
見：DBFIDDLE
只是我id從 1 開始，而不是從 0 開始。
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/439766.html
標籤：sql postgresql