SQL返回某个单表中存在相同字段值纪录中的一条纪录！

文章来源：CSDN社区 - 原文地址：http://topic.csdn.net/t/20050817/18/4214856.html
比如某个表：
id stuNo stuName stuAddress stuTel remark
1 111 aa aaa 8888 aaaaa
2 111 bb bbb 6666 bbbbb
3 111 cc ccc 6666 ccccc
4 222 dd ddd 6666 ddddd
5 333 ee eee 5555 55555

目前，stuNo字段中有三条记录（id为1，2，3）是相同的，都为“111”，请问怎么写SQL才能返回此三条记录中的随便某一个纪录呢？随便返回id为1，2，3中的某一条记录而不是返回单个字段。
方法1:
select *
from 表名 as t1
where id in
(select top 1 id
from 表名 as t2
where t1.stuno=t2.stuno
order by newid())
方法2:
select *

from 表 a

where id=(select max(id)
from 表
where stuNo=a.stuNo
)
方法3:
declare @tab table(id int,stuNo varchar(10),stuName varchar(10),stuAddress varchar(10),stuTel varchar(10),remark varchar(10))
insert @tab values(1,'111','aa','aaa','8888','aaaaa')
insert @tab values(2,'111','aa','aaa','8888','aaaaa')
insert @tab values(3,'111','aa','aaa','8888','aaaaa')
insert @tab values(4,'222','dd','ddd','6666','ddddd')
insert @tab values(5,'333','ee','eee','5555','55555')
--查看原始数据
select * from @tab


--删除重复数据
delete from @tab where id not in (select max(id) from @tab group by stuNo,stuName,stuAddress,stuTel,remark)
--查看结果
select * from @tab
方法4:
select * from table1
group by stuNo
having count(*)>1
方法5:
随机取值：

select * from 表 t
where id=
(
select top 1 id
from 表
where stuNo=t.stuNo
order by newid()
)

取id最大值:

select * from 表 t
where not exists
(
select 1 from 表
where stuNo=t.stuNo and
id>t.id
)

取id最小值:

select * from 表 t
where not exists
(
select 1 from 表
where stuNo=t.stuNo and
id<t.id
)

方法6:
select *
from 表名
where Id in (select Max(id)
from 表名 as t1
grou by stuNo
having Count(*) > 1)
随机取值也可以按 vivianfdlpw() ( ) 思路来做Top
方法7:
select *
from 表 a
where id=(select max(id)
from 表
where stuNo = a.stuNo
)
方法8:
select * from a where id in (select max(id) from a group by stuno )

or

select * from a where id in (select min(id) from a group by stuno )