多次连接相同表
|
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 |
将上两个表做连接，出下面的报表：
|
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 p.pat_id,p.ins_id1,i1.Description,p.ins_id2,i2.Description,p.ins_id3,i3.Descriptionfrom
patient p
left join INSURANCE i1 on p.ins_id1=i1.ins_id
left join INSURANCE i2 on p.ins_id2=i2.ins_id
left join INSURANCE i3 on p.ins_id3=i3.ins_id
order by p.pat_id;
因为表patient里面有三个外键（Insurance表里的主键），所以得连接三个表（三个都是Insurance表）。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/26155238/viewspace-764662/ ，如需转载，请注明出处，否则将追究法律责任。
