大家帮助我好吗很急,不知怎么办.....

[wangyongahz6]

有一表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重复的记录

[akuzou]

select * from 表A a where exists(select * from 表A where id=a.id and name=a.name)

[冷箫轻笛]

select * from 表A a
where exists(select 1 from 表A where id=a.id and name=a.name and state <> a.state)

[marco08]

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)

[marco08]

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 A.* from A
inner join
(select id, name from A group by id, name having count(*)>1)
B on A.id=B.id and A.name=B.name

--result
----------- ---- -----------
1 a 1
1 a 2
3 a 1
3 a 1

(4 row(s) affected)

[akuzou]

没测试，原来写的有问题，呵呵!
楼主请用 marco08(天道酬勤) ( ) 的

[caixia615]

都是强人速度那么快...