Case Syntax:
Exercise 1: Show gender 0, 1 as male, female
Select * fromStudentinfo--Case : Judging columns in the result set--Example 1: Display student information, gender as "male and female" display:Select *, CaseSgender when 1 Then 'male' when 0 Then 'female' End asSex fromStudentinfo
View Code
Results:
Exercise 2: Show the score as "excellent, good, medium and poor"
--Example 2: The student score is displayed as a grade >=90 excellent, >=80 good, >=60, other poorSelect *, Case whenScorevalue>= - andScorevalue< the Then 'in' whenScorevalue>= - Then 'Excellent' whenScorevalue>= the Then 'Liang' Else 'Poor' End aslevel fromScoreinfo
View Code
Exercise 3: Statistics on students ' scores in Chinese, maths, and English (view, aggregate function)
--query student name, account name, score (build view)--row to column (pivot) --statistics of students ' achievements in Chinese, maths and EnglishCreate ViewStudent_score asSelectStu.sname,sub.stitle,score.scorevalue fromScoreinfo scoreInner JoinSubjectinfo Sub onSub.sid=Score.subidInner JoinStudentinfo Stu onStu.sid=Score.stuidSelect * fromStudent_score--Query ViewExample--name Chinese maths English--Xiao Bao Bao 92.5--pivot (row to column, column change)SelectsName Name,--if the value of the current stitle is "language", the output Scorevalue CaseStitle when 'language' ThenScorevalueEndlanguages, Case whenStitle='Mathematics' ThenScorevalueEndMathematics, CaseStitle when 'English' ThenScorevalueEndEnglish fromStudent_scoreSelectsName Name,--if the value of the current stitle is "language", the output Scorevalue--multiple rows merged into one line (aggregate function)Max( CaseStitle when 'language' ThenScorevalueEnd) languages,min( Case whenStitle='Mathematics' ThenScorevalueEnd) Mathematics,sum( CaseStitle when 'English' ThenScorevalueEnd) English fromStudent_scoreGroup bySName
View Code
Exercise: Pivot
--class boys and girls--Tsing Lung 1 2--1. Create a ViewCreate ViewStudent_class_gender asSelectCtitle,sgender,COUNT(*) asCount1 fromstudentinfo StuInner JoinClassInfo class onStu.cid=Class.cidGroup byCtitle,sgenderSelect * fromStudent_class_gender--2. Conversion--Conversion--pivot (row to column)SelectCtitle, CaseSgender when 1 ThenCount1Else 0 EndMale, Case whenSgender=0 ThenCount1Else 0 Endfemale fromStudent_class_gender--3. MergerSelectCtitle,Max( CaseSgender when 1 ThenCount1Else 0 End) Male,Max( Case whenSgender=0 ThenCount1Else 0 End) Female fromStudent_class_genderGroup byCtitle
View Code
Summary of Database Review (-case) keywords (pivot)