select 姓名 from table where (select * from table where having(count) > 1
参考: --选择表中字段值相同的记录,过滤不同的记录 declare @User table(Cid int,Cname varchar(50))insert @User(Cid,Cname) values (1,'企业合同工以上') insert @User(Cid,Cname) values (2,'编外工') insert @User(Cid,Cname) values (3,'实习生') insert @User(Cid,Cname) values (4,'临时工') insert @User(Cid,Cname) values (5,'编外工')select * from @Userselect * from @User A where Exists (select 1 from @user where Cname=A.Cname having count(*)>1)select distinct cname from @User
select distinct(name) from tb where name in (select name from tb group by name having count(*)>1)
select 姓名,学历,..... from table1 where 姓名 in (select 姓名 from table1 group by 姓名 having count(*)>1)
select name,学历 from table where group by name and count(*) >1
SELECT * FROM TABLE_NAME WHERE ROWID!=(SELECT MAX(ROWID) FROM TABLE_NAME D WHERE TABLE_NAME.COL1=D.COL1 AND TABLE_NAME.COL2=D.COL2);
设每个人都有唯一编号ID Select * From Table1 A Where not Exists(Select 1 From Table1 B Where A.ID < B.ID and A.Name = B.Name)
同意 hsj20041004(光芒)的select zjxm,max(zjbh) from zjxxb group by zjxm order by zjxm测试过,这个就行
不好意思没看清楚要求,应该是 Select * From Table1 A Where not Exists(Select 1 From Table1 B Where A.ID < B.ID and A.Name = B.Name) and A.Name in (Select Name From Table1 Group By Name Having Count(1) > 1)
select 姓名,学历 from table where 姓名 in (select 姓名 from table where having(count) > 1
sorry!少了个括号.SELECT * FROM [table] where [姓名] in (SELECT [姓名] FROM [table] group by [姓名] having(count(*) > 1)
--选择表中字段值相同的记录,过滤不同的记录
declare @User table(Cid int,Cname varchar(50))insert @User(Cid,Cname)
values (1,'企业合同工以上')
insert @User(Cid,Cname)
values (2,'编外工')
insert @User(Cid,Cname)
values (3,'实习生')
insert @User(Cid,Cname)
values (4,'临时工')
insert @User(Cid,Cname)
values (5,'编外工')select * from @Userselect * from @User A
where Exists (select 1 from @user where Cname=A.Cname having count(*)>1)select distinct cname from @User
where 姓名 in (select 姓名 from table1 group by 姓名 having count(*)>1)
WHERE ROWID!=(SELECT MAX(ROWID) FROM TABLE_NAME D
WHERE TABLE_NAME.COL1=D.COL1 AND TABLE_NAME.COL2=D.COL2);
Select * From Table1 A
Where not Exists(Select 1 From Table1 B Where A.ID < B.ID and A.Name = B.Name)
Select * From Table1 A
Where not Exists(Select 1 From Table1 B Where A.ID < B.ID and A.Name = B.Name)
and A.Name in (Select Name From Table1 Group By Name Having Count(1) > 1)
where [姓名] in (SELECT [姓名] FROM [table] group by [姓名] having(count(*) > 1)