create table mytest ( sname nvarchar(10), class int )insert into mytest (sname,class) values('张',1) insert into mytest (sname,class) values('张',2) insert into mytest (sname,class) values('王',3) insert into mytest (sname,class) values('李',2) insert into mytest (sname,class) values('赵',4) insert into mytest (sname,class) values('周',3) insert into mytest (sname,class) values('张',1) insert into mytest (sname,class) values('张',2) insert into mytest (sname,class) values('李',2)SELECT * FROM mytestdeclare @cur_class int; set @cur_class=2;SELECT m.sname,m.class FROM mytest as m inner join (select sname,class from mytest where class=@cur_class GROUP BY sname,class having count(*)>1) as t on m.sname=t.sname and m.class=t.class
create table mytest
(
sname nvarchar(10),
class int
)insert into mytest (sname,class) values('张',1)
insert into mytest (sname,class) values('张',2)
insert into mytest (sname,class) values('王',3)
insert into mytest (sname,class) values('李',2)
insert into mytest (sname,class) values('赵',4)
insert into mytest (sname,class) values('周',3)
insert into mytest (sname,class) values('张',1)
insert into mytest (sname,class) values('张',2)
insert into mytest (sname,class) values('李',2)SELECT * FROM mytestdeclare @cur_class int;
set @cur_class=2;SELECT m.sname,m.class FROM mytest as m
inner join
(select sname,class from mytest where class=@cur_class GROUP BY sname,class
having count(*)>1) as t
on m.sname=t.sname and m.class=t.class