SQL SERVER 取重复值和取某列第N大值得行数据的 SQL语句
举例：创建一学生表结构如下：
create Table Student ( [id] [int] IDENTITY(1,1) NOT NULL, [name] [varchar](20) NOT NULL, [age] [int] NOT NULL )
插入如下数据：
insert into Student([name],age) values('小明',24); insert into Student([name],age) values('小红',34); insert into Student([name],age) values('小至',22); insert into Student([name],age) values('小黄',22); insert into Student([name],age) values('小王',34); insert into Student([name],age) values('小刘',26); insert into Student([name],age) values('小天',27); insert into Student([name],age) values('小地',24);
那么我们要获取Student表中年龄第3大的学生数据该怎么写查询呢？ 如下：
//方法一（只能显示一条数据） select top 1 t.* from (select TOP 3 * from Student a where a.id in(select top 1 id from Student where age =a.age ) order by age DESC,id) t order by age //方法二（可查询出所有并列第N大的数据行，如果有的话） select * from student where age =(select top 1 age from student where age in(select top 3 age from Student group by age order by age desc) order by age asc)
那我们又要获取Student表中所有同龄学生的数据该怎么写呢？如下：
select * from Student where age in(select age from Student group by age having count(age) > 1) order by age
