case when not in
时间: 2023-12-08 08:39:09 浏览: 24
在SQL中,CASE WHEN语句用于根据条件执行不同的操作。NOT IN运算符用于在WHERE子句中筛选不匹配指定值列表中任何一个值的行。下面是一个使用CASE WHEN和NOT IN的示例:
假设我们有一个名为tb_tree的表,其中包含id和p_id两列。如果p_id为空,则该行为根节点;如果id不在p_id列中,则该行为叶子节点;否则,该行为内部节点。我们可以使用以下查询来实现:
```sql
SELECT t.id,
CASE
WHEN ISNULL(t.p_id) THEN '根节点'
WHEN t.id NOT IN (SELECT a.p_id
FROM tb_tree a
WHERE p_id IS NOT NULL) THEN '叶子节点'
ELSE '内部节点'
END
FROM tb_tree t;
```
另外,如果我们想要查找姓“张”的人,可以使用LIKE运算符。如果我们只想查找以“张”开头的人,可以使用“张%”;如果我们想查找包含“张”的人,可以使用“%张%”。以下是一个使用LIKE运算符的示例:
```sql
SELECT *
FROM c_cons
WHERE cons_name LIKE '张%';
```
相关问题
使用case when not in
好的,我明白了。针对您的问题,可以使用以下语法实现:
```
SELECT column_name,
CASE WHEN column_name NOT IN (value1, value2, ...) THEN 'Other'
ELSE column_name
END
FROM table_name;
```
其中,`column_name`是要进行判断的列名,`value1, value2, ...`是不包含在判断范围内的值,`Other`是当列值不在判断范围内时的替代值。
case when sql
CASE WHEN is a conditional statement in SQL that allows you to perform different actions based on specified conditions. It is typically used in conjunction with the SELECT statement to add conditional logic to the result set.
Here's an example of how you can use the CASE WHEN statement in SQL:
```sql
SELECT column1, column2,
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
ELSE result3
END AS alias
FROM your_table;
```
In this example, you specify the conditions and corresponding results within the CASE WHEN statement. If condition1 is true, it will return result1. If condition2 is true, it will return result2. If none of the conditions are true, it will return result3.
You can also use the CASE WHEN statement without any conditions, which acts like an IF-ELSE statement:
```sql
SELECT column1, column2,
CASE
WHEN value = 1 THEN 'One'
ELSE 'Not One'
END AS alias
FROM your_table;
```
In this case, if the value is equal to 1, it will return 'One'. Otherwise, it will return 'Not One'.