我怎樣才能做一個連接兩個表并獲得結果表。考慮它有困難，因為我相信一個是水平表，另一個是垂直表。關于 SO 的其他答案對我來說并不清楚，因為我必須在一行中加入一個帶有列名的值。我怎樣才能做到這一點？
CREATE TABLE forecast (
year integer,
week integer,
model varchar(50),
category varchar(50),
subcategory varchar(50)
);
insert into forecast (year, week, model, category, subcategory) values (2021, 1, 'AAA', 'CategoryA', 'SubcategoryA');
insert into forecast (year, week, model, category, subcategory) values (2021, 1, 'BBB', 'CategoryA', 'SubcategoryA');
insert into forecast (year, week, model, category, subcategory) values (2021, 1, 'CCC', 'CategoryB', 'SubcategoryB');
insert into forecast (year, week, model, category, subcategory) values (2021, 1, 'DDD', 'CategoryA', 'SubcategoryC');
CREATE TABLE translation (
type varchar(50),
name varchar(50),
translated varchar(50)
);
insert into translation (type, name, translated) values ('category', 'CategoryA', 'TranslatedCategoryA');
insert into translation (type, name, translated) values ('category', 'CategoryB', 'TranslatedCategoryB');
insert into translation (type, name, translated) values ('subcategory', 'SubcategoryA', 'TranslatedSubcategoryA');
insert into translation (type, name, translated) values ('subcategory', 'SubcategoryB', 'TranslatedSubcategoryB');
insert into translation (type, name, translated) values ('subcategory', 'SubcategoryC', 'TranslatedSubcategoryC');
CREATE TABLE result (
year integer,
week integer,
model varchar(50),
category varchar(50),
subcategory varchar(50)
);
insert into result (year, week, model, category, subcategory) values (2021, 1, 'AAA', 'TranslatedCategoryA', 'TranslatedSubcategoryA');
insert into result (year, week, model, category, subcategory) values (2021, 1, 'BBB', 'TranslatedCategoryA', 'TranslatedSubcategoryA');
insert into result (year, week, model, category, subcategory) values (2021, 1, 'CCC', 'TranslatedCategoryB', 'TranslatedSubcategoryB');
insert into result (year, week, model, category, subcategory) values (2021, 1, 'DDD', 'TranslatedCategoryA', 'TranslatedSubcategoryC');
這
select * from forecast f
left join translation t
on t.name = f.category or t.name = f.subcategory
一次翻譯一個，這是有道理的，但我無法從中得到兩列來翻譯每一列
uj5u.com熱心網友回復：
型別和名稱的雙左連接。
如果沒有翻譯，則合并默認為原始名稱。
select f.year, f.week, f.model , coalesce(cat.translated, f.category) as category , coalesce(subcat.translated, f.subcategory) as subcategory from forecast f left join translation cat on cat.name = f.category and cat.type = 'category' left join translation subcat on subcat.name = f.subcategory and subcat.type = 'subcategory' order by f.year, f.week, f.model;
| 年 | 星期 | 模型 | 類別 | 子類別 |
|---|---|---|---|---|
| 2021 | 1 | AAA | 翻譯類別A | 翻譯子類別A |
| 2021 | 1 | BBB | 翻譯類別A | 翻譯子類別A |
| 2021 | 1 | CCC | 翻譯類別B | 翻譯子類別B |
| 2021 | 1 | DDD | 翻譯類別A | 翻譯子類別C |
db<>在這里擺弄
uj5u.com熱心網友回復：
我們必須進行多重連接。每列一個。
select f.year, f.week, f.model, t.translated as 'category', t2.translated as 'subcategory'
from forecast f
left join translation t
on t.name = f.category
left join translation t2
on t2.name = f.subcategory
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/422822.html
標籤：
上一篇：使用以下腳本合并所有