数据库开发工程师面试题

Question

如题所述

Answer 1

select LESSON_NAME as '科目'， max(case when sequence = 1 then NameGrade else null end) as '第一名(姓名分数)'， max(case when sequence = 2 then NameGrade else null end) as '第二名(姓名分数)'， max(case when sequence = 3 then NameGrade else null end) as '第三名(姓名分数)'

(

select LESSON_NAME,STU_NAME','convert(varchar,GRADE) as NameGrade,sequence

(select b.LESSON_NAME,c.STU_NAME,a.GRADE,row_number() over(order by a.GRADE Desc, c.STU_NAME asc) as sequence from score a

inner join lession b on (a.LESSION_ID = b.LESSION_ID)

inner join student c on (a.STU_ID= c.STU_ID) ) d

where sequence < 4

) e

group by LESSON_NAME

order by case(when LESSON_NAME = '语文' then 1，when LESSON_NAME = '数学' then 2，when LESSON_NAME = '英语' then 3，when LESSON_NAME = '物理' then 4，when LESSON_NAME = '化学' then 5，else 9999 end)

大概就是这个样子，没执行，你自己再调试下 。

如果两个人相同分数，根据名字顺序排列

logo设计

创造品牌价值

¥500元起

APP开发

量身定制，源码交付

¥2000元起

商标注册

一个好品牌从商标开始

¥1480元起

公司注册

注册公司全程代办

¥0元起

查

看

更

多

官方电话官方服务
官方网站八戒财税知识产权八戒服务商企业需求数字市场