SQL練習：JOIN & Sub-Query & ISNULL

PTT DataBase版 JOIN問題#1KEwsOmC

把第二張的left join 給第一張，第三張是想要的結果

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

--創建測試資料表 CREATE TABLE CUSTNO_MONEY ( CUSTNO varchar(10) NULL, BLOCNO varchar(10) NULL, MONEY int NULL ) ON [PRIMARY] insert into CUSTNO_MONEY(CUSTNO,BLOCNO,MONEY) values ('01','Z1',1000) insert into CUSTNO_MONEY(CUSTNO,BLOCNO,MONEY) values ('02','Z2',2000) insert into CUSTNO_MONEY(BLOCNO,MONEY) values ('Z3',3000) insert into CUSTNO_MONEY(BLOCNO,MONEY) values ('Z4',4000) insert into CUSTNO_MONEY(CUSTNO,MONEY) values ('05',5000) CREATE TABLE CUSTNO_EMP ( CUSTNO varchar(10) NULL, BLOCNO varchar(10) NULL, EMPCNAME varchar(10) NULL ) ON [PRIMARY] insert into CUSTNO_EMP(CUSTNO,BLOCNO,EMPCNAME) values ('01','Z1','N1') insert into CUSTNO_EMP(CUSTNO,BLOCNO,EMPCNAME) values ('02','Z2','N2') insert into CUSTNO_EMP(BLOCNO,EMPCNAME) values ('Z3','N3') insert into CUSTNO_EMP(BLOCNO,EMPCNAME) values ('Z4','N4') insert into CUSTNO_EMP(BLOCNO,EMPCNAME) values ('Z4','N5') insert into CUSTNO_EMP(CUSTNO,EMPCNAME) values ('05','N6')

解法

根據CUSTNO將第二張表併入第一張AS B1

根據BLOCNO將第二張表併入B1 AS B2

SELECT時判斷EMPCNAME欄位是否為NULL

1 2 3 4 5 6 7 8 9 10 11

--使用ISNULL SELECT CUSTNO_MONEY.*,ISNULL(B1.EMPCNAME,B2.EMPCNAME) AS EMPCNAME --判斷B1.EMPCNAME是否為NULL，若為NULL用B2.EMPCNAME替換 FROM CUSTNO_MONEY LEFT OUTER JOIN CUSTNO_EMP AS B1 ON CUSTNO_MONEY.CUSTNO = B1.CUSTNO --將第二張表併入第一張AS B1，Key：CUSTNO LEFT OUTER JOIN CUSTNO_EMP AS B2 ON CUSTNO_MONEY.BLOCNO = B2.BLOCNO --將第二張表併入第一張AS B2，Key：BLOCNO --一般查詢 select CUSTNO_EMP.CUSTNO,CUSTNO_EMP.BLOCNO,MONEY,EMPCNAME from CUSTNO_EMP left outer join CUSTNO_MONEY ON CUSTNO_EMP.CUSTNO=CUSTNO_MONEY.CUSTNO OR CUSTNO_EMP.BLOCNO=CUSTNO_MONEY.BLOCNO