Patient | Insurance | ||||
Pat_ID | Ins_ID_1 | Ins_ID_2 | Ins_ID_3 | Ins_ID | Description |
1 | 5 |
|
| 1 | Medicare |
2 | 8 |
|
| 2 | Blue Cross |
3 | 4 | 12 |
| 3 | OXFORD |
4 | 11 |
|
| 4 | 1st Health Ins |
5 | 10 | 7 | 1 | 5 | United Healthcare |
6 | 5 | 7 |
| 6 | Travellers |
7 | 3 | 7 | 2 | 7 | Medicaid |
8 | 4 | 9 | 5 | 8 | Capital Healthplan |
9 | 3 |
|
| 9 | MVP Healthcare |
10 | 1 |
|
| 10 | Harvard Healthplan |
用Join的方式来产生如下的报表,每个Ins ID后面加上相应的描述。如InsID不能在Insurance 表中发现,即用空格表示:(如ID 11 和12)
Pat_ID | InsID1 | Desc-1 | InsID2 | Desc-2 | InsID3 | Desc-3 |
1 | 5 | United Healthcare |
|
|
|
|
2 | 8 | Capital Healthplan |
|
|
|
|
3 | 4 | 1st Health Ins | 12 |
|
|
|
4 | 11 |
|
|
|
|
|
5 | 10 | Harvard Healthplan | 7 | Medicaid | 1 | Medicare |
6 | 5 | United Healthcare | 7 | Medicaid |
|
|
7 | 3 | OXFORD | 7 | Medicaid | 2 | Blue Cross |
8 | 4 | 1st Health Ins | 9 | MVP Healthcare | 5 | United Healthcare |
9 | 3 | OXFORD |
|
|
|
|
10 | 1 | Medicare |
|
|
|
|
代码:
Select pat_id,P.INS_ID_1,T.DESCRIPTION AS DE1,P.INS_ID_2,T1.DESCRIPTION AS DE2,P.INS_ID_3,T2.DESCRIPTION AS DE3
FROM PATIENT P LEFT JOIN INSURANCE T ON
P.INS_ID_1=T.INS_ID
LEFT JOIN INSURANCE T1 ON P.INS_ID_2=T1.INS_ID
LEFT JOIN INSURANCE T2 ON P.INS_ID_3=T2.INS_ID
ORDER BY PAT_ID;
LEFT JOIN就是不管右边的表中有没有此列,只要左边的表中有就进行匹配或者直接出结果。