orderby总分desc,orderby前五
sql查询问题 order by (总分/人数) desc
SELECT
*
FROM
表
order by
(总分/人数) desc
这样？
SQL数据库试题求解
------------------------------------------------------
create table students(st_id varchar(20),st_name varchar(50),sex varchar(10))
insert into students(st_id,st_name,sex)
select 'st001','张杰', '男' union all
select 'st002', '公孙燕飞' ,'男' union all
select 'st003', '王楠', '女' union all
select 'st004', '王伟', '男' union all
select 'st005','李燕纹', '女' union all
select 'st006', '孙武' ,'男'
select *
from students
create table teachers(t_id varchar(20),t_name varchar(50),t_lesson varchar(50))
insert into teachers
select 't001', '张老师' ,'数学' union all
select 't002', '李老师', '英语'
delete from results
create table results(r_id varchar(20),r_fenshu int,r_stid varchar(50),r_tid varchar(50))
insert into results
select 'r001','90', 'st001', 't002' union all
select 'r002', '68', 'st005', 't001' union all
select 'r003', '92', 'st003' ,'t001' union all
select 'r004', '82', 'st006', 't002' union all
select 'r005', '70', 'st002', 't002' union all
select 'r006', '86', 'st002', 't001' union all
select 'r007', '57', 'st003', 't002' union all
select 'r008', '76', 'st006', 't001' union all
select 'r009', '55', 'st001', 't001' union all
select 'r010', '77', 'st004', 't002' union all
select 'r011', '58', 'st005', 't002'
----------------------------------------------------------
1.
select st_id
from students
where st_name = '王伟'
2.select st_id,st_name
from students
where st_name like '__燕%'
3 select st_name,len(st_name) as 名字长度
from students
where sex ='男'
4 select min(r_fenshu) as 最低分数
from teachers t inner join results r on t.t_id =r.r_tid
where t_lesson ='数学' --这个是不考虑成绩中有null值的
5 select s.st_id as 学生编号,r_fenshu as分数,r_tid as 课目号
from students s inner join results r on s.st_id =r.r_stid
where s.sex='女'
--如果还要课目的名称的话请用下面的
select s.st_id as 学生编号,r.r_fenshu as 分数,r.r_tid as 课目号,t.t_lesson as 课目名称
from students s inner join results r on s.st_id =r.r_stid
inner join teachers t on r.r_tid = t.t_id
where s.sex='女'
6 select avg(r.r_fenshu)
from results r inner join teachers t on r.r_tid = t.t_id
where t.t_lesson='英语'
7.select *
from students s inner join results r on s.st_id =r.r_stid
inner join teachers t on r.r_tid = t.t_id
where s.st_id in (select top 2 st_id from students order by st_id desc)
order by s.st_id desc
8 select sum(r.r_fenshu) as 总分
from results r inner join students s on r.r_stid =s.st_id
where s.st_name = '王楠'
9.select distinct s.st_id,s.st_name
from students s inner join results r on s.st_id = r.r_stid
where st_id not in (select r_stid from results where r_fenshu60) and st_id not in (select r_stid from results where r_fenshu =90)
10 update results
set r_fenshu = r_fenshu + 10
--如果分数不可能大于100请用这句 set r_fenshu = case when r_fenshu + 10 =100 then r_fenshu + 10 else 100 end
where r_stid in (select st_id from students where sex='女')
1 进阶题
select t.t_name,count(*)
from students s,teachers t,results r
where r.r_tid = t.t_id
and s.st_id =r.r_stid
and r.r_fenshu = 60
and t.t_id in (select t_id from teachers where t_lesson='数学' )
--and t_lesson='数学'
group by t.t_name
2
select top 1 sum(r_fenshu) as 总分,t.t_lesson,t_id,t_name
from results r,teachers t
where r.r_tid = t.t_id
group by t.t_lesson,t_id,t_name
order by 总分 desc
3. delete from results where r_stid in (select r_stid from results group by r_stid having count(r_tid) = 1)
1 选做题
select d.name from sysobjects d where d.xtype='U'
2.select top 5 * from students order by newid()
用sql语句，查询每个班级成绩排名前三名的学生姓名
1、首先在打开的SQL?Server中，假设有两条数据中，包含有【张】，但是这个张一前一后，如下图所示。
2、此时就能利用Select和From语句，查询数据，如下图所示。
3、但是要查找姓名中包含有【张】，那么不能用等号，如下图所示。
4、因此一定要必须使用Like关键字，才能查询SQL的数据。
5、这个时候，如果单对【张】字后加个百分号是不允许的，这样只能查找姓张的数据。
6、如果想要前后都有关键字【张】，那么一定要在前后都加百分号，就能实现模糊查找姓名。
SQL server计算总分和平均分，排名问题
用以下语句
select?
????id,?姓名,?语文,?数学,?英语,?物理,?化学,?
????(语文+?数学+?英语+?物理+?化学)?As?总分,
????((语文+?数学+?英语+?物理+?化学)?/5?)As?平均分
from?
????成绩表
order?by?????总分?desc
注意，不能用sum和avg。因为这些函数是针对不同行（记录）进行计算；而现在是对同一行的不同字段进行计算。
班级均分在年级排序用一句SLQ语句如何写？
select 班级，总平均分
from
（
select 班级,avg(总分) “总分平均分” from 学生成绩
group by 班级）
order by 总平均分 desc
;
以上是oracle数据库的查询语句，供参考。
对总分排序按从高到低的顺序输出？
select *,chinese+math+english as 总分 from exam order by 总分 desc;
select *,chinese+math+english as 总分 from exam order by chinese+math+
english desc;