t sql case when - уроки и примеры использования

Конструкция case when в языке T-SQL позволяет нам принимать различные действия на основе условий, определенных в запросе. Она имеет следующий синтаксис:

CASE
    WHEN условие1 THEN действие1
    WHEN условие2 THEN действие2
    ...
    ELSE действие_по_умолчанию
END

Пример использования case when в T-SQL:

SELECT 
    Имя,
    Возраст,
    CASE
        WHEN Возраст > 18 THEN 'Совершеннолетний'
        ELSE 'Несовершеннолетний'
    END AS Статус
FROM 
    Пользователи

В этом примере, если возраст пользователя больше 18, то возвращается значение "Совершеннолетний", в противном случае возвращается значение "Несовершеннолетний". Это позволяет нам динамически классифицировать пользователей в зависимости от их возраста.

T-SQL CASE WHEN Statement: An In-depth Explanation

As a high school student aiming to deepen your knowledge in computer science, it is essential to understand various aspects of programming languages and their functionality. In this article, we will explore the T-SQL CASE WHEN statement, an important component of the Transact-SQL language used for managing relational databases.

What is the T-SQL CASE WHEN Statement?

The T-SQL CASE WHEN statement is a powerful conditional statement used in SQL queries to perform selective actions based on specified conditions. It allows us to compare a given value against multiple conditions and execute different actions based on which condition is met.

The syntax of the T-SQL CASE WHEN statement is as follows:

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE resultN
END

Let's break down the syntax:

• CASE: Begins the CASE WHEN statement.
• WHEN: Specifies the condition to check.
• THEN: Specifies the result if the condition is true.
• ELSE: Specifies the result if none of the conditions are true.
• END: Ends the CASE WHEN statement.

Using T-SQL CASE WHEN in Practice

To illustrate the usage of the T-SQL CASE WHEN statement, let's consider a scenario where we have a "Students" table with columns for "Name," "Age," and "Grade." We want to select all the students from the table and categorize them based on their age.

SELECT Name, Age,
    CASE
        WHEN Age < 12 THEN 'Child'
        WHEN Age >= 12 AND Age < 18 THEN 'Teenager'
        ELSE 'Adult'
    END AS AgeCategory
FROM Students;

In the above example, we use the T-SQL CASE WHEN statement to categorize students into three different age categories: 'Child,' 'Teenager,' and 'Adult.' The resulting output will include the student's name, age, and their respective age category.

Nested T-SQL CASE WHEN Statements

An additional feature of the T-SQL CASE WHEN statement is its ability to be nested within another T-SQL CASE WHEN statement. This allows for more complex conditional logic. Let's consider an example:

SELECT ProductName, UnitsInStock,
    CASE
        WHEN UnitsInStock > 50 THEN 'In Stock'
        WHEN UnitsInStock > 0 THEN 'Limited Stock'
        ELSE 'Out of Stock'
    END AS StockStatus,
    CASE
        WHEN UnitsInStock > 0 THEN
            CASE
                WHEN UnitsInStock <= 10 THEN 'Low Stock'
                WHEN UnitsInStock <= 30 THEN 'Medium Stock'
                ELSE 'High Stock'
            END
        ELSE 'N/A'
    END AS StockLevel
FROM Products;

In this example, we have a "Products" table with information about various products, including their name and the number of units in stock. The T-SQL CASE WHEN statement is used to categorize the stock status as 'In Stock,' 'Limited Stock,' or 'Out of Stock.' Additionally, a nested T-SQL CASE WHEN statement is used to further categorize the stock level as 'Low Stock,' 'Medium Stock,' or 'High Stock' when the product is in stock.

Conclusion

The T-SQL CASE WHEN statement is a valuable tool for performing conditional actions within SQL queries. It allows us to compare values against multiple conditions and perform different actions based on the results. By understanding how to use the T-SQL CASE WHEN statement, you can enhance your ability to manipulate and analyze data in databases effectively.

Remember to practice using the T-SQL CASE WHEN statement in your own projects to solidify your understanding and explore its versatility further.