1樓：
select b.id,a.name
from b
left join a on a.id=b.id
sql查詢兩個表相同的兩個欄位裡不同的資料有哪些
2樓：幸運的
sql語句如下：
select * from table1
full join table2 on table1.xingming = table2.xingming
where
table1.xingming is null or table2.xingming is null
分析：1、首先得出兩個表的並集
注：full join :存在匹配，匹配顯示；同時，將各個表中不匹配的資料與空資料行匹配進行顯示。可以看成是左外連線與右外連線的並集。
圖中結果左側兩列為table1，右側兩列為table2。
前三條記錄表示table1和table2都有的資料。
table1項為null的記錄說明table2中無相同項。
同理，table2項為null的記錄說明table1中無相同項。
下面，只需要設定篩選條件，過濾出所需記錄。
2、設定過濾條件，得到結果
從結果中可以看出，表1中的趙二在表2中沒有相同xingming的記錄。
表2中的劉六在表1中沒有相同xingming的記錄。
本題還有其它多種解法，此處列出比較好理解的一種。
3樓：大野瘦子
select * from a
inner join b on a.name = b.name and a.id = b.id
where a.name = '張三' and a.id = '008'
內連線即可
或者：1、除重
select distinct a.id as aid,a.name as aname,b.
id as bid,b.name as bname from a inner join b on(a.name=b.
name and a.id=b.id)
2、除重
select a.id as aid,a.name as aname,b.
id as bid,b.name as bname from a inner join b on(a.name=b.
name and a.id=b.id)
sql的其他查詢
1、查詢表中多餘的重複記錄，重複記錄是根據單個欄位（peopleid）來判斷
select * from peoplewhere peopleid in (select peopleid from people group by peopleid
having count(peopleid) > 1)
2、刪除表中多餘的重複記錄，重複記錄是根據單個欄位（peopleid）來判斷，只留有rowid最小的記錄
delete from peoplewhere peopleid in (select peopleid from people group by peopleid
having count(peopleid) > 1)and rowid not in (select min(rowid) from people group by
peopleid having count(peopleid )>1)
3、查詢表中多餘的重複記錄（多個欄位）
select * from vitae awhere (a.peopleid,a.seq) in (select peopleid,seq from vitae group by
peopleid,seq having count(*) > 1)
4、刪除表中多餘的重複記錄（多個欄位），只留有rowid最小的記錄
delete from vitae awhere (a.peopleid,a.seq) in (select peopleid,seq from vitae group by
peopleid,seq having count(*) > 1)and rowid not in (select min(rowid) from vitae group by
peopleid,seq having count(*)>1)
5、查詢表中多餘的重複記錄（多個欄位），不包含rowid最小的記錄
select * from vitae awhere (a.peopleid,a.seq) in (select peopleid,seq from vitae group by
peopleid,seq having count(*) > 1)and rowid not in (select min(rowid) from vitae group by
peopleid,seq having count(*)>1)
4樓：匿名使用者
select * from table1 minus select * from table2
union all
select * from table2 minus select * from table1
原理minus : 返回第一個表中有、第二個表中沒有的資料注意：
minus 是 oracle 裡面用的。
如果是 sql server 的話， 用 except 替換掉 minus.
5樓：匿名使用者
easy
select xingming from table1 where not exists (select 1 from table2 where xingming = table1.xingming)
union
select xingming from table2 where not exists (select 1 from table1 where xingming = table2.xingming)
6樓：笑年
select *
from table1
where table1.xingming not in (select * from table2)
union
select *
from table2
where table2.xinming not in (select * from table1)
7樓：匿名使用者
select xingming from table1 where not exists (select 1 from table2 where xingming = table1.xingming)
union
select xingming from table2 where not exists (select 1 from table1 where xingming = table2.xingming)
8樓：匿名使用者
select * from table1 where xingming not in(select xingming from table2)
9樓：綠蔥蔥
select xingming from table1 where xingming='趙二'
select xingming from table1 where xingming='馬七'
select xingming from table2 where xingming='劉六'
access資料庫兩個表關聯去重複的查詢
問題描述不夠清楚，我寫的是查詢每個使用者的max yuejifen 並列出對應a表中的編號 b表中的username,問題分解如下 1 先查a表userid,max yuejifen select userid,max yuejifen as maxyue from a group by useri...
SQL查詢出兩個資料表，再通過這兩個表的相同欄位合併成資料表，急急急
你試一下,不知對不對 呵呵 seletct 物料 期初資料,總入庫數量,總出版庫權數量,期初資料 總入庫數量 總出庫數量 as 結存資料 from select from a full join b on a.物料 b.物料 c select isnull a.物料 抄,b.物料 as 物料 a.期...
在access資料庫中如何實現兩個表之間欄位的匹配
select a.name,b.name from a1 a join b1 b on a.name b.name 試試看行不行 access資料庫如何根據兩個欄位的匹配程度進行排序 100 select user.movie.from user movie where user.m type mo...