我有如下資料
我想復制以下列：職業列不包含 N/A值的名字和姓氏。
id firstname lastname profession 1 profession 2 profession 3 profession 4 profession 5
1 firstname1 lastname1 nurse physician n/a n/a n/a
2 firstname2 lastname2 physician nurse n/a n/a n/a
3 firstname3 lastname3 n/a n/a n/a n/a n/a
4 firstname4 lastname4 nurse n/a n/a n/a n/a
5 firstname5 lastname5 physician n/a n/a n/a n/a
6 firstname6 lastname6 nurse midwife physician dentist n/a
7 firstname7 lastname7 physician midwife dentist n/a n/a
這是我需要的示例輸出。
firstname1 lastname1 nurse
firstname1 lastname1 physician
firstname2 lastname2 physician
firstname2 lastname2 nurse
firstname3 lastname3 n/a
firstname4 lastname4 nurse
firstname5 lastname5 physician
firstname6 lastname6 nurse
firstname6 lastname6 midwife
firstname6 lastname6 physician
firstname6 lastname6 dentist
firstname7 lastname7 physician
firstname7 lastname7 midwife
firstname7 lastname7 dentist
太感謝了。
uj5u.com熱心網友回復：
我們在MySQL中取消透視的方式是使用union all.
select firstname, lastname, profession_1 as profession
from t
where profession_1 <> 'n/a'
union all
select firstname, lastname, profession_2
from t
where profession_2 <> 'n/a'
union all
select firstname, lastname, profession_3
from t
where profession_3 <> 'n/a'
union all
select firstname, lastname, profession_4
from t
where profession_4 <> 'n/a'
union all
select firstname, lastname, profession_5
from t
where profession_5 <> 'n/a'
order by firstname, lastname
| 名 | 姓 | 職業 |
|---|---|---|
| 名字1 | 姓氏1 | 護士 |
| 名字1 | 姓氏1 | 醫師 |
| 名字2 | 姓氏2 | 醫師 |
| 名字2 | 姓氏2 | 護士 |
| 名字4 | 姓氏4 | 護士 |
| 名字5 | 姓氏5 | 醫師 |
| 名字6 | 姓氏6 | 護士 |
| 名字6 | 姓氏6 | 助產士 |
| 名字6 | 姓氏6 | 醫師 |
| 名字6 | 姓氏6 | 牙醫 |
| 名字7 | 姓氏7 | 醫師 |
| 名字7 | 姓氏7 | 助產士 |
| 名字7 | 姓氏7 | 牙醫 |
小提琴
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/507708.html
上一篇：如何在多表查詢中使用組合主鍵
下一篇：組平SQL查詢結果