CREATE PROCEDURE sp_middle_per
AS
select a.id,a.name,a.sfz
from zginfo a
where Not EXISTS
(select zginfo.sfz
from zginfo,middle_per
where middle_per.id=zginfo.id and middle_per.name=zginfo.name and middle_per.sfz=zginfo.sfz And a.sfz=zginfo.sfz )
AS
select a.id,a.name,a.sfz
from zginfo a
where Not EXISTS
(select zginfo.sfz
from zginfo,middle_per
where middle_per.id=zginfo.id and middle_per.name=zginfo.name and middle_per.sfz=zginfo.sfz And a.sfz=zginfo.sfz )
from zginfo
where Isnull(sfz,'') not in
(select Isnull(zginfo.sfz ,'')
from zginfo,middle_per
where middle_per.id=zginfo.id and middle_per.name=zginfo.name and middle_per.sfz=zginfo.sfz)
比如你想取得zginfo表的10584条记录,如果想取middle_per表的,则用RIGHT JOIN
select zginfo.sfz
from zginfo LEFT JOIN middle_per ON
middle_per.id=zginfo.id and middle_per.name=zginfo.name and middle_per.sfz=zginfo.sfz
select zginfo.sfz
from zginfo,middle_per
where middle_per.id=zginfo.id and middle_per.name=zginfo.name and middle_per.sfz=zginfo.sfz
之外的的记录
我再说详细点。表middle_per是效验表,zginfo有三个字段ID(社保号),name(姓名),sfz(身份证),通过对比效验表,如果能在middle_per中找到ID,NAME,SFZ都一致的数据,就通过,放到一边不管,如果找不到全部一致的就挑出处理。
分不够在加。谢谢!!
Frewin的方法用NOT EXISTS检索不到数据,用ISNULL和不用ISNULL检索出的是一样的。
是 is null
中间有个空格,ok
select sfz from zginfo和select distinct sfz from zginfo检索出的结果数不等。
但怎么把重复值检索出来呢?
select sfz,相同数目=count(sfz)
from zginfo aa
where (select count(sfz) from zginfo where sfz=aa.sfz group by sfz)>1
group by sfz