表:table1
字段:a b c id
a <NULL> a 1
a <NULL> a 8
b b b 9
b c b 10
b b b 11
a b b 12
提取重复的记录,不合并
要得到
a <NULL> a 1
a <NULL> a 8
b b b 9
b b b 11我用
select * from table1 A where
exists (select 1 from table1 where a=a.a and b=a.b and c=a.c and id<>A.id)
可是无法提取NULL值
字段:a b c id
a <NULL> a 1
a <NULL> a 8
b b b 9
b c b 10
b b b 11
a b b 12
提取重复的记录,不合并
要得到
a <NULL> a 1
a <NULL> a 8
b b b 9
b b b 11我用
select * from table1 A where
exists (select 1 from table1 where a=a.a and b=a.b and c=a.c and id<>A.id)
可是无法提取NULL值
字段:a b c id
a <NULL> a 1
a <NULL> a 8
b b b 9
b c b 10
b b b 11
a b b 12
提取重复的记录,不合并
要得到
a <NULL> a 1
a <NULL> a 8
b b b 9
b b b 11我用
select * from table1 A where
exists (select 1 from table1 where a=a.a and b=a.b and c=a.c and id<>A.id)
可是无法提取NULL值
字段:a b c id
a <NULL> a 1
a <NULL> a 8
b b b 9
b c b 10
b b b 11
a b b 12
提取重复的记录,不合并
要得到
a <NULL> a 1
a <NULL> a 8
b b b 9
b b b 11我用
select * from table1 A where
exists (select 1 from table1 where a=a.a and b=a.b and c=a.c and id<>A.id)
可是无法提取NULL值
group by a, b, c
having count(*)>1
exists
(select 1 from table1 where isnull(a,'')=isnull(a.a,'') and isnull(b,'')=isnull(a.b,'')
and isnull(c,'')=isnull(a.c,'') and isnull(id,0)<>isnull(A.id,0))
exists(select 1 from T where isnull(a, '')=isnull(a.a, '') and isnull(b, '')=isnull(a.b, '') and isnull(c, '')=isnull(a.c, '') and id<>A.id)
insert T select 'a', NULL, 'a', 1
union all select 'a', NULL, 'a', 8
union all select 'b', 'b', 'b', 9
union all select 'b', 'c', 'b', 10
union all select 'b', 'b', 'b', 11
union all select 'a', 'b', 'b', 12
select * from T A where
exists(select 1 from T where isnull(a, '')=isnull(a.a, '') and isnull(b, '')=isnull(a.b, '') and isnull(c, '')=isnull(a.c, '') and id<>A.id)--result
a b c id
---- ---- ---- -----------
a NULL a 1
a NULL a 8
b b b 9
b b b 11(4 row(s) affected)
exists(select 1 from T where isnull(a, '')=isnull(a.a, '') and isnull(b, '')=isnull(a.b, '') and isnull(c, '')=isnull(a.c, '') and id<>A.id)ISNULL还有点用啊