我有一張如下表：
ID | Fileid1 | Fileid2 | Fileid3 | LanguageID1 | LanguageID2 | LanguageID3
---------------------------------------------------------------------------
1 | 123 | 256 | | a12 | a23 |
2 | 124 | | | a12 | |
這里LanguageID1對應Fileid1,LanguageID2對應Fileid2等等。
我想將其轉換為以下內容：
ID | Field | LangaugeID
1 | 123 | a12
2 | 256 | a23
3 | 124 | a12
我已經嘗試UNPIVOT在這兩個欄位上使用，但它給出了所有列之間的關系。應該怎么做？
uj5u.com熱心網友回復：
您可以使用聯合：
select row_number() over(order by ID, Fileid) as ID,
Fileid,
LanguageID
from
(select ID, Fileid1 as Fileid, LanguageID1 as LanguageID from table_name
union all select ID, Fileid2 as Fileid, LanguageID2 as LanguageID from table_name
union all select ID, Fileid3 as Fileid, LanguageID3 as LanguageID from table_name) t
where Fileid is not null
小提琴
uj5u.com熱心網友回復：
你可以試試：
select id, Fileid, LanguageID
from (
select id,fileid1 as Fileid,LanguageID1 as LanguageID
from my_table
union all
select id,fileid2 as Fileid ,LanguageID2 as LanguageID
from my_table
union all
select id,fileid3 as Fileid ,LanguageID3 as LanguageID
from my_table
) as t1
where concat_ws('',Fileid,LanguageID) !=''
演示
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/433458.html
上一篇：如何使用nodejs從mysql資料庫中獲取最新的10條記錄？
下一篇：空列不回傳NULL或空