Case application in MSSQL (2) [SQL Server]

Use the case function in check

Using the case function in check is a good solution in many cases. There may be a lot of people who don't need check at all, so I suggest you try to use check in SQL after reading the example below.
Here is an example:
Company A, the company has a rule that the wages of female employees must be higher than 1000 yuan. If you use check and case, the statement is as follows:

CONSTRAINT check_salary CHECK
           ( CASE WHEN sex = '2'
                  THEN CASE WHEN salary > 1000
                        THEN 1 ELSE 0 END
                  ELSE 1 END = 1 ) 

If you simply use check, as shown below:

CONSTRAINT check_salary CHECK
           ( sex = '2' AND salary > 1000 ) 

 

The conditions of the female employee are met, and the male employee cannot enter the information.

 

6. Use the sum function in the case Function

False X has the following table.

Student ID (class_id) Course name (class_name) Major (main_class_fl)

-------------------------------------------------------------
100 1 economics y
100 2 history n
200 2 history n
200 3 archaeology y
200 4 Computer n
300 4 Computer n
400 5 chemistry n
500 6 mathematics n

Some students choose to take several courses (100,200) at the same time, and some students choose only one course (300,400,500 ). If you are a student of multiple courses, You must select a course as your major. your major is Y in FL. Students who only select one course, majoring in FL as N (in fact, if I write y, there will be no troubles below. For example, please include more ).
Now we need to query this table according to the following two conditions: the person who takes only one course, returns the ID of the course, the person who takes multiple courses, and returns the ID of the selected main course. The simple idea is to execute two different SQL statements for query.

-- Condition 1: only students who have selected one course

SELECT std_id, MAX(class_id) AS main_class
     FROM Studentclass
GROUP BY std_id
     HAVING COUNT(*) = 1;

Execution result 1
Std_id main_class
----------------
300 4
400 5
500 6

-- Condition 2: Select multiple course students

SELECT std_id, class_id AS main_class
    FROM Studentclass
WHERE main_class_fl = 'Y' ;

 

Result 2
Std_id main_class
----------------
100 1
200 3

If the case function is used, we only need one SQL statement to solve the problem, as shown below:

Code

 Select std_id,
Case when count (*) = 1 -- select only the students of one course
Then max (class_id)
Else max (case when main_class_fl = 'y'
Then class_id
Else null end
)
End as main_class
From studentclass
Group by std_id;

Running result
Std_id main_class
----------------
100 1
200 3
300 4
400 5
500 6

By embedding case functions in case functions and using case functions in aggregate functions, we can easily solve this problem. The use of the Case function gives us greater freedom. Finally, I would like to remind the novice who uses the case function not to make the following mistakes:

Case col_1
When 1 then 'right'
When null then 'wrong'
End

In this statement, the when Null Line always returns unknown, so wrong will never occur. This statement can be replaced with when col_1 = NULL. This is an incorrect usage. In this case, we should use when col_1 is null.