- case when then else end
Case when 语句能在sql语句中植入语句判断的逻辑,类似于if else语句
1 case 字段 when 什么时候 then 结果 else 结果2 end
sex 为 1 等于男 0 等于女 该怎么将数字转化成汉字呢?
SQL : select name,(case sex when 0 then “女” when 1 then “男” end)sex from table
2 第二种编写方式,使用表达式来做显示
case
when 条件 then result1 else result2
end
如果条件为true 返回result1 不符合条件的返回为null 可以有多个when then end表示是结束,
场景:score 大于90为优秀,80以上为良好,60是及格 其他的不及格,
SQL:select name,score,(case
when score>=90 then “优秀”
when score>=80 then “良好”
when score>=60 then “及格”
else “不及格”
end) level from table
3 将列转化成行
SQL:SELECT
name,
MAX(CASE course WHEN ‘语文’ THEN score ELSE 0 END) AS ‘语文’,
max(CASE course WHEN ‘数学’ THEN score ELSE 0 END) AS ‘数学’,
max(CASE course WHEN ‘英语’ THEN score ELSE 0 END) AS ‘英语’,
AVG(score) AS ‘平均成绩’
FROM score GROUP BY name;
-
if
if函数可以通过判断条件来返回特定值,
语法:if(表达式,结果1,结果2)
根据表达式 true返回结果1,false 返回结果2
例子:select name,if(sex = 1,“男”,“女”)sex from table -
ifnull
为了防止null的尴尬 用特定的值来代替null
select ifnull(price,0)price from goods where name =“light”; -
case的位置
示例
在select 之后 主要是控制显示
只显示在城市的人名和工资
SELECT name,case when city = '北京' THEN salary END FROM EMP
where 之后
-- 如果在北京 那么只显示工资大于6666的信息 如果 如果在在上海只显示大于4k的
select * from EMP where (case when city = '北京' then salary end )>6666 or (case when city = '上海' then salary end)>4000
- 解决字符集不匹配的问题
select case when htbh ‘0’ then Translate(‘无’ USING NCHAR_CS) else htbh end from VIEW_EU_HTQC_VALID - union 解决字符集不匹配的问题 和添加一个无的数据
select to_char(case when dummy= 'X' then '无' else dummy end) as htbh from dual
union all
select to_char(htbh) from VIEW_EU_HTQC_VALID
WHERE ISEND = 1 ORDER BY HTBH DESC
========================
SELECT '无' AS HTBH,'无' AS HTMC FROM dual
UNION ALL
SELECT TO_CHAR(HTBH),TO_CHAR(HTMC) FROM VIEW_EU_HTQC_VALID
WHERE ISEND = 1