现有一个表student
userid name sex age
1 a 1 10
2 b 0 12
3 c 1 10
4 d 1 15
5 e 0 16
6 f 0 9
7 g 1 12
8 a 0 15
9 b 1 12
10 a 1 18想得到如下结果:如果name相同,userid不同的,都显示出来
userid name sex age
1 a 1 10
8 a 0 15
10 a 1 18
2 b 0 12
9 b 1 12
请大家帮忙了!
userid name sex age
1 a 1 10
2 b 0 12
3 c 1 10
4 d 1 15
5 e 0 16
6 f 0 9
7 g 1 12
8 a 0 15
9 b 1 12
10 a 1 18想得到如下结果:如果name相同,userid不同的,都显示出来
userid name sex age
1 a 1 10
8 a 0 15
10 a 1 18
2 b 0 12
9 b 1 12
请大家帮忙了!
insert into @t select 1 , 'a' , 1 , 10
union all select 2 , 'b' , 0 , 12
union all select 3 , 'c' , 1 , 10
union all select 4 , 'd' , 1 , 15
union all select 5 , 'e' , 0 , 16
union all select 6 , 'f' , 0 , 9
union all select 7 , 'g' , 1 , 12
union all select 8 , 'a' , 0 , 15
union all select 9 , 'b' , 1 , 12
union all select 10 , 'a' , 1 , 18
select * from @t a where exists(select * from @t where name=a.name and userid<>a.userid) order by name,userid
(
userid int,
name char(1),
sex int,
age int
)
insert into #t select 1,'a',1,10
union all select 2,'b',0,12
union all select 3,'c',1,10
union all select 4,'d' , 1 , 15
union all select 5 , 'e' , 0 , 16
union all select 6 , 'f' , 0 , 9
union all select 7 , 'g' , 1 , 12
union all select 8 , 'a' , 0 , 15
union all select 9 , 'b' , 1 , 12
union all select 10 , 'a' , 1 , 18
select * from #t where name in(select name from #t a group by name having count(name)>1) order by name