select studentname from tb group by studentname having(count(studentid))>1
create table T (studentid int ,studentname varchar(10),studentcore int) insert into T select 1,'李明',99 insert into T select 2,'張三',98 insert into T select 3,'王五',97 insert into T select 4,'張三',94 insert into T select 5,'趙六',94 insert into T select 6,'趙六',94select studentname from T group by studentname having count(*)>1 /* studentname ------------------ 張三 趙六*/drop table T
create table tb(studentid int, studentname varchar(32), studentcore int) insert tb select 1,'李明',99 union all select 2,'张三',98 union all select 3,'王五',97 union all select 4,'张三',94 select studentname from tb group by studentname having(count(studentid))>1drop table tb/* studentname -------------------------------- 张三(1 row(s) affected) */
显示全部重复记录的信息: declare @T table (studentid int ,studentname varchar(10),studentcore int) insert into @T select 1,'李明',99 insert into @T select 2,'張三',98 insert into @T select 3,'王五',97 insert into @T select 4,'張三',94 insert into @T select 5,'趙六',94 insert into @T select 6,'趙六',94 select * from @T t where (select count(1) from @T where studentname=t.studentname)>1studentid studentname studentcore ----------- ----------- ----------- 2 張三 98 4 張三 94 5 趙六 94 6 趙六 94(所影响的行数为 4 行)
select studentname from tb group by studentname having(count(studentid))>1
select distinct a.name from [Table] a,[Table] b where a.name=b.name and a.id<>b.id
create table T (studentid int ,studentname varchar(10),studentcore int)
insert into T select 1,'李明',99
insert into T select 2,'張三',98
insert into T select 3,'王五',97
insert into T select 4,'張三',94
insert into T select 5,'趙六',94
insert into T select 6,'趙六',94select studentname
from T
group by studentname
having count(*)>1
/*
studentname
------------------
張三
趙六*/drop table T
insert tb select 1,'李明',99
union all select 2,'张三',98
union all select 3,'王五',97
union all select 4,'张三',94 select studentname from tb group by studentname having(count(studentid))>1drop table tb/*
studentname
--------------------------------
张三(1 row(s) affected)
*/
declare @T table (studentid int ,studentname varchar(10),studentcore int)
insert into @T select 1,'李明',99
insert into @T select 2,'張三',98
insert into @T select 3,'王五',97
insert into @T select 4,'張三',94
insert into @T select 5,'趙六',94
insert into @T select 6,'趙六',94
select
*
from
@T t
where (select count(1) from @T where studentname=t.studentname)>1studentid studentname studentcore
----------- ----------- -----------
2 張三 98
4 張三 94
5 趙六 94
6 趙六 94(所影响的行数为 4 行)