日期:2014-05-18  浏览次数:20714 次

求重复记录查询的SQL语句,急!
表格式如下:
id name pay
1 aaa 1
2 aaa 2
3 ccc 3
4 ddd 2
5 ccc 5
6 eee 3
7 aaa 3
8 ddd 2

如果name相同,而pay不一致,则返回对应的数据;

即返回的记录是 ID=1,2,3,5,7的数据

------解决方案--------------------
SQL code
SELECT ID FROM TABLE AS T
WHERE EXISTS (SELECT * FROM TABLE WHERE T.NAME = NAME AND T.PAY <> PAY)

------解决方案--------------------
贴错了运行结果,
SQL code
IF OBJECT_ID('[test]') IS NOT NULL DROP TABLE [test]
GO 
CREATE TABLE [test]([id] int,[name] varchar(10),[pay] int)
insert into [test]
select 1,'aaa',1 union all
select 2,'aaa',2 union all
select 3,'ccc',3 union all
select 4,'ddd',2 union all
select 5,'ccc',5 union all
select 6,'eee',3 union all
select 7,'aaa',3 union all
select 8,'ddd',2

select * from [test] t
where exists ( select 1 from [test] where t.[name]=[name] and t.[pay]<>[pay])

/*
(8 row(s) affected)
id          name       pay
----------- ---------- -----------
1           aaa        1
2           aaa        2
3           ccc        3
5           ccc        5
7           aaa        3

(5 row(s) affected)
*/