1

## LEFT JOIN 後有辦法去除所有重複的資料嗎

EX: 尋找A1欄位01的資料

(A left join B
on A.A1 = B.B1)

\$sql="select A1,A2,B2,B3
from A
left join B
on A.A1 = B.B1
where (A1='\$A1' and A3=0 ) and A2 <> B2";

EX:重複的三和四會顯示其中一筆，十八和十九也是....

Mangma iT邦新手 5 級 ‧ 2020-04-29 22:34:21 檢舉

### 1 個回答

3
rogeryao
iT邦大師 1 級 ‧ 2020-04-29 22:38:59

``````CREATE TABLE A(A1 nvarchar(10),A2 int);
``````
``````insert into A
values ('01',10032),
('01',540),
('01',9481),
('01',1235),
('01',10034),
('01',6659),
('01',6134);
``````
``````CREATE TABLE B(B1 nvarchar(10),B2 int);
``````
``````insert into B
values ('01',10032),
('01',3684),
('01',1500),
('01',9481),
('01',10031),
('01',6134),
('01',6666);
``````
``````select *
from A
full outer join B on A.A1=B.B1 and A.A2=B.B2
where (A.A2 is null
or B.B2 is null)
``````

Demo Update

``````select *
from A
full outer join B on A.A1=B.B1 and A.A2=B.B2
where (A.A2 is null
or B.B2 is null)
and (A.A1='\$A1' and A.A3=0)
``````
Mangma iT邦新手 5 級 ‧ 2020-04-29 23:35:40 檢舉

full outer join返回AB的所有紀錄不論條件

WHERE 1 = 1 永真

A先和B完全連結，然後將A2=B2相同的疊上

A left join B
on A.A1 = B.B1

A full outer join B
on A.A2=B.B2

\$sql="select A1,A2,B2,B3
from A
left join B
on A.A1 = B.B1
where 1=1 and(A1='\$A1' and A3=0 )
and A.A2 is null
or B.B2 is null ";

A1和B1兩編號先結合
(A left join B
on A.A1 = B.B1WHERE A1=\$A1)

Mangma iT邦新手 5 級 ‧ 2020-04-30 00:54:15 檢舉