select * from a where sfzh not in (select sfzh from b)
select sfzh from a where sfzh not in (select sfzh from b)
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
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
select * from a where sfzh not in (select sfzh from b)
select * from a where sfzh not in (select sfzh from b)
select sfzh from a where sfzh execpt select sfzh from b
select sfzh from a execpt select sfzh from b
select a.* from a left join b on a.sfz = b.sfz where b.sfz is null
/* 将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
单用NOT IN 应该不能解决问题吧.
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
select * from a where sfzh not in (select sfzh from b)
select * from a where sfzh not in(select sfzh from b)
select * from a where sfzh not in (select sfzh from b)
select * from a where sfzh not in (select sfzh from b)
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
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
execpt
select sfzh from b
select sfzh from a execpt select sfzh from b
where b.sfz is null
/* 将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
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