分组查找次大值

Question

SELECT A.*,B.score
FROM student AS A
    JOIN record AS B
        ON A.number=B.number
WHERE 2=(SELECT COUNT(*)+1
         FROM record AS C
             JOIN student AS D
                 ON C.number=D.number
         WHERE D.class=A.class
             AND B.score<score)

Answer 1

create table record 
( 
number int, 
score int 
) 
insert into record values (1,10) 
insert into record values(2,20) 
insert into record values(3,30) 
insert into record values (4,40) 
insert into record values(5,40) 
insert into record values (6,80) create table student 
( 
number int, 
name varchar(20), 
class varchar(20) 
) 
insert into student values(1,'张三','001') 
insert into student values(2,'李四','001') 
insert into student values(3,'王五','002') 
insert into student values(4,'钱六','002') 
insert into student values(5,'张三2','001') 
insert into student values(6,'李四2','001')
;with t
as
(select s.*,rowid = row_number() over (partition by s.class order by r.score desc)
 from student s
 left join record r on s.number = r.number)
select * from t where rowid= 2drop table student,record
/*
number      name                 class                rowid
----------- -------------------- -------------------- --------------------
5           张三2                  001                  2
3           王五                   002                  2(2 行受影响)
*/

Answer 2

select *
from student a,record b
where a.number=b.number
and (select count(1) from record where class=a.class and score>b.score)+1=2---结果---
number      name                 class                number      score       
----------- -------------------- -------------------- ----------- ----------- 
4           钱六                   002                  4           40
5           张三2                  001                  5           40（所影响的行数为 2 行）

Answer 3

有缺陷，插入以下语句结果不对：
insert into student values(7,'李四22','002')
insert into student values(8,'李四222','002')
insert into record values (7,30)
insert into record values (8,40)

Answer 4

抱歉，稿错了一个表
select 
  a.number,a.name,a.class,b.score
from 
  student a,record b
where 
  a.number=b.number
and (select count(1) 
     from (select a.number,a.name,a.class,b.score from student a,record b where a.number=b.number) t 
     where class=a.class and score>b.score
    )+1=2---结果---
number      name                 class                score       
----------- -------------------- -------------------- ----------- 
3           王五                   002                  30
5           张三2                  001                  40（所影响的行数为 2 行）

Answer 5

josy上面的解决方案在插入以下语句结果不对： 
insert into student values(7,'李四22','002') 
insert into student values(8,'李四222','002') 
insert into record values (7,30) 
insert into record values (8,40)

Answer 6

同分的时候.这样做..SELECT A.*,B.score
FROM student AS A
    JOIN record AS B
        ON A.number=B.number
WHERE 2=(SELECT COUNT(DISTINCT C.score)+1
         FROM record AS C
             JOIN student AS D
                 ON C.number=D.number
         WHERE D.class=A.class
             AND B.score<score)
ORDER BY class,score,number

Answer 7

create table record 
( 
number int, 
score int 
) 
insert into record values (1,10) 
insert into record values(2,20) 
insert into record values(3,30) 
insert into record values (4,40) 
insert into record values(5,40) 
insert into record values (6,80) create table student1 
( 
number int, 
name varchar(20), 
class varchar(20) 
) 
insert into student1 values(1,'张三','001') 
insert into student1 values(2,'李四','001') 
insert into student1 values(3,'王五','002') 
insert into student1 values(4,'钱六','002') 
insert into student1 values(5,'张三2','001') 
insert into student1 values(6,'李四2','001')
select name from
(
select C.*,ROW_NUMBER()over(partition by class order by score desc) as rank1 from 
(select A.*,B.score from student1 A,record B where A.number=B.number) C
) D
where D.rank1=2

Answer 8

大致思路如下：
---测试数据---
create table record 
( 
number int, 
score int 
) 
insert into record values (1,10) 
insert into record values(2,20) 
insert into record values(3,30) 
insert into record values (4,40) 
insert into record values(5,40) 
insert into record values (6,80) create table student 
( 
number int, 
name varchar(20), 
class varchar(20) 
) 
insert into student values(1,'张三','001') 
insert into student values(2,'李四','001') 
insert into student values(3,'王五','002') 
insert into student values(4,'钱六','002') 
insert into student values(5,'张三2','001') 
insert into student values(6,'李四2','001')
insert into student values(7,'李四22','002') 
insert into student values(8,'李四222','002') 
insert into record values (7,30) 
insert into record values (8,40)
 --1、把record和student联接
select a.number,a.name,a.class,b.score 
into #a
from student a,record b 
where a.number=b.number--2、加上排名
select number,name,class,score,
 (select count(distinct score)+1 from #a where class=t.class and score>t.score) cnt
from #a t
order by class,cnt
/**
number      name                 class                score       cnt         
----------- -------------------- -------------------- ----------- ----------- 
6           李四2                  001                  80          1
5           张三2                  001                  40          2
2           李四                   001                  20          3
1           张三                   001                  10          4
4           钱六                   002                  40          1
8           李四222                002                  40          1
7           李四22                 002                  30          2
3           王五                   002                  30          2
**/--3、取第二名
select number,name,class,score
from #a t
where (select count(distinct score)+1 from #a where class=t.class and score>t.score)=2
/**
number      name                 class                score       
----------- -------------------- -------------------- ----------- 
3           王五                   002                  30
5           张三2                  001                  40
7           李四22                 002                  30
**/

Answer 9

合成一句SQL就是select number,name,class,score
from (select a.number,a.name,a.class,b.score from student a,record b where a.number=b.number) t
where 
  (select count(distinct score)+1 
   from (select a.number,a.name,a.class,b.score from student a,record b where a.number=b.number) tt 
   where class=t.class and score>t.score
  )=2
/**
number      name                 class                score       
----------- -------------------- -------------------- ----------- 
3           王五                   002                  30
5           张三2                  001                  40
7           李四22                 002                  30
**/