有一表A
id name state
1 a 1
1 a 2
2 b 1
2 c 2
3 a 1
3 a 1
4 g 1
我想得到:
id name state
1 a 1
1 a 2
3 a 1
3 a 1
就是要的到id,和name重复的记录
------解决方案--------------------
select * from 表A a
where exists(select 1 from 表A where id=a.id and name=a.name and state <> a.state)
------解决方案--------------------
create table A(id int, name char(1), state int)
insert A select 1, 'a ', 1
union all select 1, 'a ', 2
union all select 2, 'b ', 1
union all select 2, 'c ', 2
union all select 3, 'a ', 1
union all select 3, 'a ', 1
union all select 4, 'g ', 1
select * from A as tmpA
where (select count(*) from A where tmpA.id=id and tmpA.name=name)> 1
--result
id name state
----------- ---- -----------
1 a 1
1 a 2
3 a 1
3 a 1
(4 row(s) affected)