這是我的架構：
CREATE TABLE artists
(
id serial PRIMARY KEY,
name varchar(40) NOT NULL UNIQUE
);
CREATE TABLE albums
(
id serial PRIMARY KEY,
name varchar(40) NOT NULL,
artist integer NOT NULL,
FOREIGN KEY(artist) REFERENCES artists(id)
);
CREATE TABLE songs
(
id serial PRIMARY KEY,
name varchar(40) NOT NULL,
album integer NOT NULL,
FOREIGN KEY(album) REFERENCES albums(id)
);
這就是我正在做的事情：
INSERT INTO artists (name)
VALUES ('Dio') ON CONFLICT DO NOTHING;
INSERT INTO artists (name)
VALUES ('Amorphis') ON CONFLICT DO NOTHING;
SELECT id FROM artists WHERE name = 'Dio';
SELECT id FROM artists WHERE name = 'Amorphis';
INSERT INTO albums (name, artist) VALUES ('Holy Diver', 1) ON CONFLICT DO NOTHING;
INSERT INTO albums (name, artist) VALUES ('Dream Evil', 1) ON CONFLICT DO NOTHING;
INSERT INTO albums (name, artist) VALUES ('Halo', 2) ON CONFLICT DO NOTHING;
現在，我通過查看藝術家 ID 表并手動輸入來插入專輯。我怎樣才能自動做到這一點？
我的應用程式將收到歌曲名稱、藝術家姓名和專輯名稱。因此，如果藝術家存在，則需要查找藝術家 ID，如果不存在，則創建藝術家，然后獲取該藝術家的 ID 并放入INSERT INTO專輯宣告中。
uj5u.com熱心網友回復：
單程：
INSERT INTO albums (name, artist)
SELECT 'Holy Diver' AS album_name, id AS artist
FROM artists
WHERE name = 'Dio'
ON CONFLICT DO NOTHING;
另一種同時制作同一藝術家的多張專輯的方法。您可以在 VALUES 定義中列出它們。
INSERT INTO albums (name, artist)
SELECT album_name, id AS artist
FROM artists,
(
VALUES ('Holy Diver'),
('Dream Evil')
) t(album_name)
WHERE name = 'Dio'
ON CONFLICT DO NOTHING;
另一種衡量標準的方法：
INSERT INTO albums (name, artist)
SELECT album_name, id AS artist
FROM artists,
(SELECT 'Holy Diver' AS album_name
UNION
SELECT 'Dream Evil') t
WHERE name = 'Dio'
ON CONFLICT DO NOTHING;
另一種方式：
INSERT INTO albums (name, artist)
SELECT UNNEST(ARRAY['Holy Diver','Dream Evil']) AS album_name,
id AS artist
FROM artists
WHERE name = 'Dio'
ON CONFLICT DO NOTHING;
好的，我會停下來。
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/431095.html
標籤：sql PostgreSQL
上一篇：sql查詢獲取兩個日期之間的資料