2017-01-10 11:06:16       个评论    来源：qq_36074150的博客
我要投稿

# 优化MySchoolSQL编程第三章

```/****** Script for SelectTopNRows command from SSMS  ******/
SELECT TOP 1000 [StudentNo]
,[StudentName]
,[Gender]
,[Phone]
,[Birthday]
,[Email]
,[MyTT]
FROM [MySchool].[dbo].[Student]

select * from dbo.Subject

--查询oop课程
declare  @sbjec  int
select  @sbjec=SubjectId from dbo.Subject  where SubjectName='oop'

--最近一次考试时间
--select * from dbo.Result
declare @datetime  datetime
select @datetime=MAX(ExamDate) from dbo.Result where SubjectId=@sbjec

select * from dbo.Result where SubjectId=@sbjec and ExamDate=@datetime

--投影出符合的人数
declare @sum  int
select @sum=COUNT(StudentNo) from dbo.Result where SubjectId=@sbjec and ExamDate=@datetime and StudentResult<60

while(@sum>0)  --每人加两分
begin
update dbo.Result set StudentResult=StudentResult+2 where SubjectId=@sbjec and ExamDate=@datetime and StudentResult<95

select  @sum=COUNT(StudentNo) from dbo.Result where SubjectId=@sbjec and ExamDate=@datetime and StudentResult<70
end

--1到100之间的偶数之和
--(第一种方法)
declare @sun int
set @sun=1
declare  @num int
set @num=0
while(@sun<=100)
begin
if(@sun%2=0)
begin
set	@num=@num+@sun
end
set  @sun=@sun+1

end
print @num

--(第二种方法)
declare @num1 int
set @num1=0
declare @sum1 int
set @sum1=2

while(@sum1<=100)
begin
if(@sum1%2=0)
begin
set	@num1=@num1+@sum1
end
set @sum1=@sum1+1
end
print @num1

---------------***************------------------------------

declare @row  int
set @row=1
declare @i int
set @i=1
declare @chr nvarchar(32)
set @chr=''
while(@row<=5)
begin
while(@i<=@row)
begin
set  @chr+='★'
set  @i+=1
end
print @chr
set  @row+=1
end
-------------(2)-------------
declare @j int
set  @j=1
declare @xing nvarchar(32)
set @xing='★'
while( @j<=5)
begin
print  @xing
set @xing+='★'
set  @j+=1
end

--------------------------CASE  end---多分支语句------------------------------
--查询oop课程
declare  @sbjecc  int
select  @sbjecc=SubjectId from dbo.Subject  where SubjectName='oop'

--最近一次考试时间
--select * from dbo.Result
declare @datet  datetime
select @datet=MAX(ExamDate) from dbo.Result where SubjectId=@sbjecc

select StudentName,等级=
case
when StudentResult>90 then 'A'
when StudentResult>80 then 'B'
when StudentResult>=70 then 'C'
when StudentResult>60 then 'D'
else
'E'
end
from dbo.Student S,dbo.Result F where S.StudentNo=F.StudentNo
and ExamDate=@datet and SubjectId=@sbjecc

--------------------------SQL语句面试题，关于group by-------------------------------

create table tmp(rq varchar(10),shengfu nchar(1))
select * from tmp

insert into tmp values('2005-05-09','胜')
insert into tmp values('2005-05-09','胜')
insert into tmp values('2005-05-09','负')
insert into tmp values('2005-05-09','负')
insert into tmp values('2005-05-10','胜')
insert into tmp values('2005-05-10','负')
insert into tmp values('2005-05-10','负')

select rq as 时间 ,
sum(
case
when shengfu='胜' then 1
else 0
end) as 胜,
sum(
case
when shengfu='负' then 1
else 0
end) as 负
from tmp
group by rq
```