我的数据库是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条
图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条
inner join in_union_his b
on a.key_jobno =b.key_jobno
left join esc_union_his c
on b.id=c.id
left join activityrecord d
on c.id=d.id
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
每个表都有主键,他们之间仅以keyjobno关联吗?一次性查询,虽然都有keyjobno,但存在冗余是不可避免的
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
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
你这里的第1列1005,和第2列的小明还是返回了多行相同的记录。我只要求返回一行。还有,你用in_union_his表来做left join,即,你是用记录数最多的表放在第一位才行,这样一来就不灵活了,那么我每次做查询都要去判定那个表的记录数最多才可进行查询。
其实我设计数据库是这样的,welfare是主表,其它3张表是子表,welfare只返回一行记录就行了,其它表关联后不显示重复记录,重复的为空或null;hdhai9451你可以用ACCESS数据库来建表测试吗?我数据是ACCESS的,不支持ROW_NUMBER,partition by函数,如果你用mysql或sql server来做的话,即使达到了效果,语句在ACCESS不通过,对我还是没用的
welfare是主表,其它3张表是子表,子表的记录可以随意添加,welfare表每个keyjobno只能有一条记录,即welfare表的keyjobno是主键,welfare只返回一行记录就行了,其它表关联后不显示重复记录,重复的为空或null;