Tricks to use Case-when in where clause
来源:互联网 发布:linux下xampp mysql 编辑:程序博客网 时间:2024/06/06 05:11
Tricks to useCase-when in where clause
1. Problem:
Given:
DECLARE @PeopleTABLE(
Namenvarchar(100),
Gradenvarchar(30))
DECLARE @Levelnvarchar(30)
Write a query to
If @Level='Excellent', return all@People.Grade='A'
If @Level='Good', return all @People.Grade='B'
If @Level='TopTwo', return all @People.Grade='A'OR 'B'
2. Solution:
2.1 Idea1: compose condition expression in case-when
SELECT * FROM@People
WHERE
CASE@Level
WHEN'Excellent' THEN Grade='A'
WHEN'Good' THEN Grade='B'
WHEN'TopTwo' THEN Grade IN ('A','B')
ELSE1=0
END
Incorrect syntaxnear '=', maybe because only valueexpression is supported after 'THEN'
2.2 Idea2: prepare valuelist in case-when
SELECT * FROM@People
WHEREGrade IN
(
CASE@Level
WHEN'Excellent' THEN 'A'
WHEN'Good' THEN 'B'
WHEN'TopTwo' THEN ('A','B')
ELSE'NONE'
END
)
Incorrect syntaxnear ',', not support
2.3 Idea3: use 1 or 0 in the THEN of CASE-WHEN
SELECT * FROM People
WHERE1=(
CASE
WHEN@Level='Excellent' AND Grade='A' THEN 1
WHEN@Level='Good' AND Grade='B' THEN 1
WHEN@Level='TopTwo' AND Grade IN ('A','B') THEN 1
ELSE0
END
)
- Tricks to use Case-when in where clause
- When to use actor in libgdx?
- When to Use Static Classes in C#
- When to use LinkedList and When to use ArrayList- An answer in StackOverFlow
- Unknown column 'rownum' in 'where clause'解决方案
- Column 'id' in where clause is ambiguous
- Column 'id' in where clause is ambiguous
- Unknown column '安妮宝贝' in 'where clause'
- Column 'id' in where clause is ambiguous
- Unknown column '??????' in 'where clause'问题
- Unknown column 'xxx' in 'where clause'错误
- Unknown column 'ORGANIZATION_BANK' in 'where clause'
- 中文乱码 unknown column in 'where clause'
- Column 'Email' in where clause is ambiguous
- Column 'id' in where clause is ambiguous
- SQL Query execution sequence in WHERE clause
- Column 'is_valid' in where clause is ambiguous
- Column 'id' in where clause is ambiguous
- c++ stl栈容器stack的pop(),push()等用法介绍及头文件
- 大数据架构
- springMVC 配置过程,及问题处理
- pg数据库(PostgreSQL)与gp数据库(GreenPlumSQL)的区别与联系
- 计算帧数错误记录
- Tricks to use Case-when in where clause
- C++复习之冒泡排序&插入排序&希尔排序
- Netty系列之Netty高性能之道
- 我对Java Serializable(序列化)的理解和总结
- JS 两个input(单价和数量)输入,动态改变总价
- 同时操作两张表的数据,如何做到保证数据一致性
- web程序出现异常时实现页面跳转
- MySQL查询语句练习题,测试基本够用了
- Android框架:Retrofit2之第一集简单应用