高级查询 复杂练习
来源:互联网 发布:淘宝直播镜像怎么下载 编辑:程序博客网 时间:2024/06/05 08:51
declare @date datetime --查询最近一次的考试时间
declare @subid int --查询‘oop’的科目编号
select @subid=subjectid from subject where SubjectName='oop'
select @date = max(examdate) from result where SubjectId=@subid
declare @n int
while (1=1)
begin
select @n=count(*) from Result where StudentResult<60 and SubjectId=@date
if(@n>0)
update result set studentresult=studentresult+2 where SubjectId=@subid and ExamDate=@date and StudentResult<95
else
break
declare @subid int --查询‘oop’的科目编号
select @subid=subjectid from subject where SubjectName='oop'
select @date = max(examdate) from result where SubjectId=@subid
declare @n int
while (1=1)
begin
select @n=count(*) from Result where StudentResult<60 and SubjectId=@date
if(@n>0)
update result set studentresult=studentresult+2 where SubjectId=@subid and ExamDate=@date and StudentResult<95
else
break
end
print '修改后的成绩是'
select studentname,studentresult from student
inner join result on student.StudentNo=result.StudentNo
where Subjectid=@subid and ExamDate=@date
select StudentName as 学生姓名 ,Birthday as 生日 from student where birthday > (select birthday from student where StudentName='微冷的雨')
-- 查询最近一次考试‘oop’考试 的 最低分 和 最高分
select max(studentresult) as 最高分,min(studentresult) as 最低分
from result where
ExamDate=(select max(ExamDate) from result where SubjectId=(select subjectid from subject where SubjectName='oop') )
and SubjectId=(select subjectid from subject where SubjectName='oop')
-- 查询最近一次考试‘oop’考试 的 最低分 和 最高分
select max(studentresult) as 最高分,min(studentresult) as 最低分
from result where
ExamDate=(select max(ExamDate) from result where SubjectId=(select subjectid from subject where SubjectName='oop') )
and SubjectId=(select subjectid from subject where SubjectName='oop')
--检查‘oop’课程最近一次考试。如果有分以上的成绩,则没人提分;
--否则,每人提分。最终的成绩不得大于分
if exists
(
select studentresult from Result
where SubjectId=
(
select SubjectId from Subject
where SubjectName='oop'
)
and examdate=
(select max(examdate) from result
where SubjectId=
(
select subjectid from subject
where subjectname='oop'
)
and studentresult >80
)
)
begin
--有,每人提分99
update result set StudentResult=100
where SubjectId=
(
select SubjectId from subject
where subjectname ='oop'
)
and ExamDate=
(
select max(ExamDate) from result
where subjectid =
(
select subjectid from subject
where subjectname='oop'
)
)
and studentresult >98
update result set StudentResult+=2
where subjectid=
(
select subjectid from subject
where subjectname='oop'
)
and examdate=
(
select max(ExamDate) from result
where SubjectId=
(
select SubjectId from subject
where SubjectName='oop'
)
)
and studentresult <=98
end
else
begin
--没有 ,整体+5
update result set StudentResult+=5
where SubjectId=
(
select SubjectId from subject
where SubjectName='oop'
)
and ExamDate=
(
select MAX(ExamDate) from Result
where SubjectId=
(
select SubjectId from Subject
where subjectname='oop'
)
)
end
--分页 双top 双 order by 每页显示3条数据
select top 3 * from student
where StudentNo not in
( select top 3 StudentNo from student )
select * from Student
select top 3 * from student
where StudentNo not in
( select top 3 StudentNo from student )
select * from Student
0 0
- 高级查询 复杂练习
- SQL复杂查询练习
- oracle之高级查询练习
- Mysql高级查询语句练习
- hibernate如何简单的去复杂的高级查询
- sql语句两个练习 emp dept 复杂查询
- 复杂查询
- 复杂查询
- 复杂查询
- 复杂查询
- 复杂查询
- C复杂声明练习
- Solr学习总结(六)SolrNet的高级用法(复杂查询,分页,高亮,Facet查询)
- 高级查询
- 高级查询
- 高级查询
- 高级查询
- 高级查询
- No module named 'html.entities'; 'html' is not a package
- ROS进二阶学习笔记(8)-- 关于rospy 和 parameters
- MFC中函数GetWindowRect()和GetClientRect()的区别
- 公安大数据应用之情报分析与关联挖掘
- Handler系列之原理分析
- 高级查询 复杂练习
- linux虚拟机挂载本地window系统盘
- Setup Factory打包时实现第三方DLL文件的注册
- 【Git 教程(9)】 Git 服务器搭建
- linux中nginx挂载php-fpm
- jquery实现监听文本框
- cmd下获取指定进程名的pid号,并通过taskkill结束该进程
- java Iterator(未完成)
- iOS Socket的使用