declare @t table(a varchar(100),b varchar(100))
insert @t select 'abc', '1110'
union all select 'abc', '1110'
union all select 'book', '5678'
union all select 'book', '8876'
union all select 'love', '2333'
union all select 'love', '2333'
union all select 'bike', '5555'
union all select 'bike', '5676'select * from @t a where exists(select 1 from @t where a=a.a and b<>a.b)
insert @t select 'abc', '1110'
union all select 'abc', '1110'
union all select 'book', '5678'
union all select 'book', '8876'
union all select 'love', '2333'
union all select 'love', '2333'
union all select 'bike', '5555'
union all select 'bike', '5676'select * from @t a where exists(select 1 from @t where a=a.a and b<>a.b)
insert @t select 'abc', '1110'
union all select 'abc', '1110'
union all select 'book', '5678'
union all select 'book', '8876'
union all select 'love', '2333'
union all select 'love', '2333'
union all select 'bike', '5555'
union all select 'bike', '5676'select * from @t c where exists(select * from @t where a=c.a and b!=c.b)(所影响的行数为 8 行)a b
-------------------- --------------------
book 5678
book 8876
bike 5555
bike 5676(所影响的行数为 4 行)
declare @t table(a varchar(100),b varchar(100))
insert @t select 'abc', '1110'
union all select 'abc', '1110'
union all select 'book', '5678'
union all select 'book', '8876'
union all select 'book', '8876' /*增加此行测试数据*/
union all select 'love', '2333'
union all select 'love', '2333'
union all select 'bike', '5555'
union all select 'bike', '5676'--楼上的方法
select * from @t a where exists(select 1 from @t where a=a.a and b<>a.b)--正确的方法
select * from @t x where not exists(select 1 from @t where a = x.a group by a,b having count(*) > 1)/*结果
a b
------------------------------
book 5678
book 8876
book 8876 --楼上的方法仍然会查询出b重复的行
bike 5555
bike 5676a b
------------------------------
bike 5555
bike 5676
*/
WHERE EXISTS(SELECT COUNT(1) FROM 表 WHERE 字段1 = T.字段1 AND 字段2 <> T.字段2)
WHERE EXISTS(SELECT 1 FROM 表 WHERE 字段1 = T.字段1 AND 字段2 <> T.字段2)
from
(Select Distinct * from @t)TT
where (select count(1) from (Select Distinct * from @t)T where T.a=TT.a)>1
declare @s table(a varchar(100),b varchar(100),c varchar(10))
insert @s select 'abcd', '1110', 'F'
union all select 'abcd', '1110', 'F'
union all select 'book', '5678', 'F'
union all select 'book', '8876', 'S'
union all select 'book', '8876', 'F' /*增加此行测试数据*/
union all select 'love', '2333', 'S'
union all select 'love', '2333', 'F'
union all select 'love', '2333', 'E'
union all select 'bike', '5555', 'F'
union all select 'bike', '5676', 'D'
union all select 'bike', '7890', 'F'
union all select 'bike', '5555', 'T'
union all select 'like', '5555', 'T'
union all select 'like', '5005', 'F'declare @t table(a varchar(100),b varchar(100))
insert @t select a,b from @s-----正解:
select * from @t a where exists(select 1 from @t where a=a.a and b<>a.b)
正解如下:
=======================================================
--创建测试数据
declare @s table(a varchar(100),b varchar(100),c varchar(10))
insert @s select 'abcd', '1110', 'F'
union all select 'abcd', '1110', 'F'
union all select 'book', '5678', 'F'
union all select 'book', '8876', 'S'
union all select 'book', '8876', 'F'
union all select 'love', '2333', 'S'
union all select 'love', '2333', 'F'
union all select 'love', '2333', 'E'
union all select 'bike', '5555', 'F'
union all select 'bike', '5676', 'D'
union all select 'bike', '7890', 'F'
union all select 'bike', '5555', 'T'
union all select 'like', '5555', 'T'
union all select 'like', '5005', 'F'declare @t table(a varchar(100),b varchar(100))
insert @t select a,b from @s-----正解-----
select *
from
(Select Distinct * from @t)TT
where (select count(1) from (Select Distinct * from @t)T where T.a=TT.a)>1