SQL練習：JOIN & Sub-Query

PTT DataBase版 JOIN問題#1HwCXcJp

A表 名字主檔
ID   NAME
----------
01   PETER
02   JOHN
03   MARK

B表 每個ID對應的工作
ID   JOB
----------
01   規劃
01   安裝
01   測試
02   規劃
02   安裝
03   安裝
03   測試

請問要怎麼樣做JOIN或是subquery才能讓B表每一種JOB中的ID(也就是SELECT ID GROUP BY JOB)都對應一次A表

預計結果(A+B)

B表中只有三種JOB:規劃、安裝、測試

ID    NAME    JOB

-------------------

01    PETER   規劃

02    JOHN    規劃

03    MARK    NULL

01    PETER   安裝

02    JOHN    安裝

03    MARK    安裝

01    PETER   測試

02    JOHN    NULL

03    MARK    測試

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

--創建測試資料表 CREATE TABLE A ( ID nchar(10) NULL, NAME nchar(10) NULL ) ON [PRIMARY] insert into A (ID,NAME) values ('01','PETER') insert into A (ID,NAME) values ('02','JOHN') insert into A (ID,NAME) values ('03','MARK') CREATE TABLE B ( ID nchar(10) NULL, JOB nchar(10) NULL ) ON [PRIMARY] insert into B (ID,JOB) values ('01','規劃') insert into B (ID,JOB) values ('01','安裝') insert into B (ID,JOB) values ('01','測試') insert into B (ID,JOB) values ('02','規劃') insert into B (ID,JOB) values ('02','安裝') insert into B (ID,JOB) values ('03','安裝') insert into B (ID,JOB) values ('03','測試')

解法
先將B的JOB分別對應01,02,03，再將A、B表合併入C表排序

1 2 3 4 5

select C.ID,A.NAME,B.JOB From (select distinct A.ID,JOB from B,A) as C --先將B的JOB分別對應01,02,03，將此TABLE設為C left outer join A ON C.ID=A.ID --將A表合併入C表 left outer join B ON C.ID=B.ID AND C.JOB=B.JOB --將B表合併入C表，需要C表的ID+JOB吻合才合併 order By C.JOB asc,C.ID asc