在我的專案中，前端需要遵循這種格式的 JSON 資料。所以我為用戶和他們的地址創建了兩個表，
{
"data":{
"user":{
"cif":"102345678",
"username":"user_00002",
"fname":"Kevin",
"lname":"",
"contactDetails":{
"permanentAddress":{
"Line1":"no 1",
"Line2":"cross lane",
"city":"dilhi"
},
"correspondenceAddress":{
"Line1":"no 2",
"Line2":"main street",
"city":"dilhi"
},
"mobile":"32323",
"email":"[email protected]"
}
}
}
}
所以我打算使用這個表結構，
用戶表
------------ ------- ------- ------------ --------
| USERNAME | FNAME | LNAME | EMAIL | MOBILE |
------------ ------- ------- ------------ --------
| user_00002 | Jhone | sean | [email protected] | 32323 |
------------ ------- ------- ------------ --------
| user_00003 | Kevin | Niga | [email protected] | 23232 |
------------ ------- ------- ------------ --------
| user_00005 | Mal | Ruvaw | [email protected] | 34343 |
------------ ------- ------- ------------ --------
用戶地址
------------ ---------------- ------- ------------- ----------
| USERNAME | ADDRESS_TYPE | LINE1 | LINE2 | CITY |
------------ ---------------- ------- ------------- ----------
| user_00002 | PERMANENT | no 1 | cross lane | dilhi |
------------ ---------------- ------- ------------- ----------
| user_00002 | CORRESPONDENSE | no 2 | main street | dilhi |
------------ ---------------- ------- ------------- ----------
| user_00003 | PERMANENT | no 33 | cross lane | jakartha |
------------ ---------------- ------- ------------- ----------
| user_00003 | CORRESPONDENSE | no 35 | main street | jakartha |
------------ ---------------- ------- ------------- ----------
我這樣創建地址表是因為，每個用戶都有兩個地址，一個是永久的，另一個是對應的。那么這個好的表結構可以處理這個問題嗎？如果是，那么我需要從 sql 查詢中獲得以下輸出，我該怎么做？
uj5u.com熱心網友回復：
根據我的經驗，您的表結構對我來說似乎是正確的。您可以嘗試先旋轉地址表，然后使用 user_table 加入表 -
SELECT *
FROM USER_TABLE U
JOIN (SELECT USERNAME,
MAX(CASE WHEN ADDRESS_TYPE = 'PERMANENT' THEN LINE1 ELSE NULL END) PR_LINE1,
MAX(CASE WHEN ADDRESS_TYPE = 'PERMANENT' THEN LINE2 ELSE NULL END) PR_LINE2,
MAX(CASE WHEN ADDRESS_TYPE = 'PERMANENT' THEN CITY ELSE NULL END) PR_CITY,
MAX(CASE WHEN ADDRESS_TYPE = 'CORRESPONDENSE' THEN LINE1 ELSE NULL END) COR_LINE1,
MAX(CASE WHEN ADDRESS_TYPE = 'CORRESPONDENSE' THEN LINE2 ELSE NULL END) COR_LINE2,
MAX(CASE WHEN ADDRESS_TYPE = 'CORRESPONDENSE' THEN CITY ELSE NULL END) COR_CITY
FROM USER_ADDRESS
GROUP BY USERNAME) AD ON U.USERNAME = AD.USERNAME;
轉載請註明出處，本文鏈接：https://www.uj5u.com/qianduan/361889.html