SqlServer uses case when to solve the problem of multi-condition fuzzy query,

SqlServer uses case when to solve the problem of multi-condition fuzzy query,

During project development, we often encounter multi-condition fuzzy queries. In this regard, we have two common solutions: first, concatenate an SQL string at the program end, and construct the corresponding SQL string based on whether a certain condition is selected; second, use dynamic SQL statements during database storage. In essence, it is also a concatenation of SQL strings, but it is only transferred from the program end to the Database End.

The disadvantages of the two methods are obvious: first, when multiple conditions can be empty, multiple if statements should be used for judgment; second, concatenated SQL statements are prone to the SQL injection vulnerability.

The case when statement is often used when writing database stored procedures recently. This statement can be used to solve the above problems. Taking the NorthWind database in SQL as an example, I want to operate on the Employees table. The default data in this table is as follows:

Use the following script to query table data:

Code

 DECLARE @FirstName NVARCHAR(),      @LastName  NVARCHAR();  SELECT @FirstName = '',      @LastName = '';  SELECT *  FROM  Employees c  WHERE CHARINDEX(        (          CASE            WHEN @FirstName = '' THEN FirstName           ELSE @FirstName         END       ),       FirstName     ) >      AND CHARINDEX(         (CASE WHEN @LastName = '' THEN LastName ELSE @LastName END),         LastName       ) > 

After execution, you will find that the results are the same as those in Figure 1.

We will try to assign the @ FirstName variable in the second row to 'n'. All records containing the string 'n' in the FirstName field will be found, for example:

If we try to assign the value of @ LastName in the third row to 'D', all records with the FirstName field containing 'N' and the LastName field containing 'D' will be found, for example:

Through the above example, we can see that fuzzy query can be performed based on multiple conditions by passing different values to the two variables. If the above statements are written in the stored procedure, you don't have to splice SQL statements, and there will be no injection problems.

Simple Description of the above script: replace like with the charindex function to avoid concatenating SQL statements. Use the case when statement to make the condition always true when the passed parameter value is a null string, it is equal to ignoring this condition. If it is not an empty string, fuzzy query is performed based on the parameter value.

The above is a summary of the work experience and I hope to help you. There are some more practical usage of case when. If you have time, write it again.

The following describes the usage of SQL CASE multi-condition.

Case has two formats. Simple Case functions and Case search functions.

-- Simple Case function CASE sex WHEN ''then'' male 'when'' then'' female 'else' other 'ends -- Case search function case when sex = ''then'' Male' WHEN sex = ''then'' female 'else' others' END

These two methods can achieve the same function. Simple Case functions are relatively simple in writing, but compared with Case search functions, there are some functional limitations, such as writing Case functions.

Note that the Case function returns only the first value that meets the condition, and the rest of the Case will be automatically ignored.

-- For example, in the following SQL statement, you will never get the result of the second type.

Case when col _ IN ('A', 'B') then' first class 'when col _ IN ('A') then' Second Class 'else' additional 'end'

Let's take a look at what we can do with the Case function.

1. Known Data is grouped and analyzed in another way.

There are the following data: (for better understanding, I did not use country code, but directly use the country name as the Primary Key)

Country population (population)

China

USA

Canada

UK

France

Japan

Germany

Mexico

India

The population of Asia and North America is counted based on the population data of this country. The following result is returned.

Continent population

Asia

North America

Others

What do you do if you want to solve this problem? Generating a View with continent Code is a solution, but it is difficult to dynamically change the statistical method.

If the Case function is used, the SQL code is as follows:

Select sum (population ), CASE country WHEN 'China' then' Asia 'when' India 'then' Asia 'when' Japan 'then' Asia 'when' USA 'then' North America 'when' Canada 'then' north America 'when' Mexico 'then' North America 'else' miscellaneous 'endfrom Table_AGROUP by case country when' China 'then' Asia 'when' India 'then' Asia 'when' Japan 'then 'Asian 'when' 'then' North America 'when' Canada 'then' North America 'when' Mexico 'then' North America 'else' other 'ends;

Similarly, we can use this method to determine the wage level and count the number of people at each level. The SQL code is as follows;

SELECT    CASE WHEN salary <= THEN ''       WHEN salary > AND salary <=  THEN ''       WHEN salary > AND salary <=  THEN ''       WHEN salary > AND salary <= THEN ''    ELSE NULL END salary_class,    COUNT(*)FROM  Table_AGROUP BY    CASE WHEN salary <= THEN ''       WHEN salary > AND salary <=  THEN ''       WHEN salary > AND salary <=  THEN ''       WHEN salary > AND salary <= THEN ''    ELSE NULL END;

2. Use an SQL statement to group different conditions.

The following data is available:

Country sex population (population)

China

China

USA

USA

Canada

Canada

UK

UK

Group by country and gender. The result is as follows:

National male

China

USA

Canada

UK

In normal cases, UNION can also be used to query with a statement. However, this will increase the consumption (two Select clauses), and the SQL statement will be relatively long.

The following is an example of using the Case function to complete this function.

SELECT country, SUM (case when sex = ''then population else end), -- male population SUM (case when sex = ''then population else end) -- female population FROM Table_AGROUP BY country;

In this way, Select is used to complete the output form of the explain table, which fully shows the power of the Case function.

3. 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.

The following is an example.

Company A, the company has A rule that the salary of A female employee must be higher than A dollar. If you use Check and Case

CONSTRAINT check_salary CHECK      ( CASE WHEN sex = ''         THEN CASE WHEN salary >             THEN ELSE END         ELSE END = )

If you simply use Check, as shown below:

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

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

* *** Example: <br> SELECT (case when t. name = 'name' THEN 'OK' ELSE 'no' END) AS myCom, jname FROM t <br> defines a new field, this field is used to display different display results of field results, similar to switch... case

Articles you may be interested in:

• Use the case when statement in MySQL to implement multi-condition Query