--if ELSE
--Features:
--1. No {}, use Begin...end
--2. There are multiple and nested
--3.if must have a processing statement, and if there is only one sentence, you may not use begin. End, containing only the following sentence
--4. No concept of true/false, only conditional expressions that use relational operators
The parentheses behind the--5.if can have no
--Calculates the office average score and outputs, if the average score exceeds 60 of the three students who output the highest scores, otherwise the output of the three students
Go
declare @subjectname nvarchar () = ' Office '--account name
DECLARE @subjectId int= (select Subjectid
from subject where [email protected])--Get Account ID
DECLARE @avg INT--average of subjects
Set @avg = (select AVG (studentresult) from Result
where [email protected] and studentresult are NOT null)--Gets the average score for the specified account
--making judgments
if (@avg >=95)
Begin
print ' good grades. Output top three '
Select Top 3 * from Result where [email protected]
And Studentresult is not NULL for ORDER by Studentresult Desc
End
Else
Begin
print ' bad grades. Three after output '
Select Top 3 * from Result where [email protected]
And Studentresult is not NULL for order by Studentresult
End
-------------
Go
declare @name nvarchar () = ' Office '--account name
declare @id int = (select Subjectid from Subject where subjectname [email protected])--Account ID
declare @avg int = (select AVG (studentresult) from Result
where Subjectid [email protected] and studentresult are NOT null)--Gets the average score for the specified account
--making judgments
if (@avg >=95)
Begin
print ' good results, top three output '
Select top 3*from Result where Subjectid [email protected] and studentresult is not NULL
ORDER BY Studentresult Desc
End
Else
Begin
print ' bad score, three after output '
Select Top 3 *from Result where Subjectid [email protected] and studentresult is not NULL
ORDER BY Studentresult ASC
End
If ELSE in SQL, without {}, use Begin...end