怎样查询出a表里除去b表的数据?

Question

怎样查询出a表里除去b表的数据?
a表有字段sfzh(身份证号),b表也有字段sfzh(身份证号)
怎样查询出a表里有但b表里没有的sfzh(身份证号)？全部是大陆身份证号
另外最好考虑15位和18位的问题。

Answer 1

select * from a where sfzh not in (select sfzh from b)

Answer 2

select sfzh from a where sfzh not in (select sfzh from b)

Answer 3

SELECT * 
FROM a 
WHERE NOT EXISTS(SELECT 1 FROM b WHERE dbo.FN_GetNewIdCard(a.sfzh) = dbo.FN_GetNewIdCard(b.sfzh))go
/*使用以下用户函数*/
/* 将15位身份证号码转换成18位身份证号码 */
CREATE FUNCTION FN_GetNewIdCard(@SFZH VARCHAR(18))
RETURNS CHAR(18)
AS 
BEGIN
DECLARE @R CHAR(1)
DECLARE @I INT
IF LEN(@SFZH) = 15
BEGIN
SET @SFZH = STUFF(@SFZH, 7, 0, '19')

SET @i = cast(substring(@SFZH,1,1) as int) * 7
+cast(substring(@SFZH,2,1) as int) * 9
+cast(substring(@SFZH,3,1) as int) * 10
+cast(substring(@SFZH,4,1) as int) * 5
+cast(substring(@SFZH,5,1) as int) * 8
+cast(substring(@SFZH,6,1) as int) * 4
+cast(substring(@SFZH,7,1) as int) * 2
+cast(substring(@SFZH,8,1) as int) * 1
+cast(substring(@SFZH,9,1) as int) * 6
+cast(substring(@SFZH,10,1) as int) * 3
+cast(substring(@SFZH,11,1) as int) * 7
+cast(substring(@SFZH,12,1) as int) * 9
+cast(substring(@SFZH,13,1) as int) * 10
+cast(substring(@SFZH,14,1) as int) * 5
+cast(substring(@SFZH,15,1) as int) * 8
+cast(substring(@SFZH,16,1) as int) * 4 
+cast(substring(@SFZH,17,1) as int) * 2
SET @i = @i - @i/11 * 11
SET @r = (case @i 
when 0 then '1' 
when 1 then '0' 
when 2 then 'X' 
when 3 then '9'
when 4 then '8' 
when 5 then '7' 
when 6 then '6' 
when 7 then '5'
when 8 then '4' 
when 9 then '3' 
when 10 then '2' 
else '/' end)
SET @SFZH = @SFZH + @R
END
RETURN @SFZH
END
go

Answer 4

select * from a where not exists(select * from b 
where (case when len(a.sfzh)=18 then left(stuff(a.sfzh,7,2,''),15) else a.sfzh end)=
(case when len(b.sfzh)=18 then left(stuff(b.sfzh,7,2,''),15) else b.sfzh end))
-----------------------------
create table a(sfzh varchar(18)) 
create table b(sfzh varchar(18)) 
insert a select '110110192806121231'
insert b select '110110280612123'insert a select '110110192906121231'insert a select '110110300612123'
insert b select '110110193006121231'
-----------------------------
110110192906121231

Answer 5

select * from a where sfzh not in (select sfzh from b)

Answer 6

select * from a where sfzh not in (select sfzh from b) 

Answer 7

select sfzh  from a where sfzh 
execpt
select sfzh from b 

Answer 8

select sfzh  from a execpt select sfzh from b 

Answer 9

select a.* from a left join b on a.sfz = b.sfz
where  b.sfz is null

Answer 10

/* 将15位身份证号码转换成18位身份证号码 */
CREATE FUNCTION FN_GetNewIdCard(@SFZH VARCHAR(18))
RETURNS CHAR(18)
AS 
BEGIN
    DECLARE @R CHAR(1)
    DECLARE @I INT
    IF LEN(@SFZH) = 15
    BEGIN
        SET @SFZH = STUFF(@SFZH, 7, 0, '19')
    
        SET @i = cast(substring(@SFZH,1,1) as int) * 7
            +cast(substring(@SFZH,2,1) as int) * 9
            +cast(substring(@SFZH,3,1) as int) * 10
            +cast(substring(@SFZH,4,1) as int) * 5
            +cast(substring(@SFZH,5,1) as int) * 8
            +cast(substring(@SFZH,6,1) as int) * 4
            +cast(substring(@SFZH,7,1) as int) * 2
            +cast(substring(@SFZH,8,1) as int) * 1
            +cast(substring(@SFZH,9,1) as int) * 6
            +cast(substring(@SFZH,10,1) as int) * 3
            +cast(substring(@SFZH,11,1) as int) * 7
            +cast(substring(@SFZH,12,1) as int) * 9
            +cast(substring(@SFZH,13,1) as int) * 10
            +cast(substring(@SFZH,14,1) as int) * 5
            +cast(substring(@SFZH,15,1) as int) * 8
            +cast(substring(@SFZH,16,1) as int) * 4 
            +cast(substring(@SFZH,17,1) as int) * 2
        SET @i = @i - @i/11 * 11
        SET @r = (case @i 
                when 0 then '1' 
                when 1 then '0' 
                when 2 then 'X' 
                when 3 then '9'
                when 4 then '8' 
                when 5 then '7' 
                when 6 then '6' 
                when 7 then '5'
                when 8 then '4' 
                when 9 then '3' 
                when 10 then '2' 
                else '/' end)
        SET @SFZH = @SFZH + @R
    END
    RETURN @SFZH
END
goSELECT * FROM a 
WHERE NOT EXISTS(SELECT 1 FROM b WHERE dbo.FN_GetNewIdCard(a.sfzh) = dbo.FN_GetNewIdCard(b.sfzh))go

Answer 11

单用NOT IN 应该不能解决问题吧.

Answer 12

set nocount on
declare @a table([Name]varchar(10),Code varchar(20))
insert @a select 'A','320324198006180649'
insert @a select 'B','320324198006180650'
insert @a select 'C','320324198006180651'
insert @a select 'E','320324198006180653'
insert @a select 'F','320324198006180654'
declare @b table([Name]varchar(10),Code varchar(20))
insert @b select 'A','320324198006180649'
insert @b select 'B','320324198006180650'
insert @b select 'C','320324198006180651'
insert @b select 'D','320324198006180652'
select a.* from @a a where not exists(select 1 from @b where code=a.code)Name       Code
---------- --------------------
E          320324198006180653
F          320324198006180654

Answer 13

select * from a where sfzh not in (select sfzh from b)

Answer 14

select * from a where sfzh not in(select sfzh from b)

Answer 15

select * from a where sfzh not in (select sfzh from b) 

Answer 16

select * from a where sfzh not in (select sfzh from b)