SQL语句多表关联去除重复的记录
我的数据库是ACCESS,现在我有多张表，现在我用keyjobno关联了,keyjobno是我的关联字段，但当中有重复的记录，怎样去除重复的记录？请教高手，我花了很多时间都没解决到，急啊·············下面附上图1、图2，图1：
图1的结果集是我想要的结果
图2：
图2是我用语句查询出来结果，但不符合我想要的结果。这是我的语句
select a.keyjobno,a.name,getdate,b.indate,b.createdate,c.escdate,d.activityname,joindate from
((welfare a left join in_union_his b on a.keyjobno=b.keyjobno) left join esc_union_his c on
a.keyjobno=c.keyjobno) left join activityrecord d on a.keyjobno=d.keyjobno
要求：要用ACCESS数据库做，每张表如果有相同的记录只显示1条
[解决办法]
你没有关键字关联，那么得自己加上行号，试试:
select a.keyjobno,a.name,getdate,b.indate,b.createdate,c.escdate,d.activityname,joindate
from (select *,rn=ROW_NUMBER()over(partition by keyjobno order by getdate()) from in_union_his) a
left join (select *,rn=ROW_NUMBER()over(partition by keyjobno order by getdate()) from welfare) b on a.keyjobno=b.keyjobno and a.rn=b.rn
left join (select *,rn=ROW_NUMBER()over(partition by keyjobno order by getdate()) from esc_union_his) c on a.keyjobno=c.keyjobno and a.rn=c.rn
left join (select *,rn=ROW_NUMBER()over(partition by keyjobno order by getdate()) from activityrecord) d on a.keyjobno=d.keyjobno and a.rn=d.rn
SELECT a.keyjobno ,
a.name ,
getdate ,
b.indate ,
b.createdate ,
c.escdate ,
d.activityname ,
joindate
FROM welfare a
INNER JOIN in_union_his b ON a.keyjobno = b.keyjobno
LEFT JOIN esc_union_his c ON a.keyjobno = c.keyjobno
AND B.ID = C.ID
LEFT JOIN activityrecord d ON a.keyjobno = d.keyjobno
AND B.ID = D.ID
