case when then else_SQL进阶case语句

SQL进阶-1-case语句

认真把SQL语言提升下，选择了日本的一本书籍。本文中记录的是关于case语句的用法

• 行列转换
• 已有数据的重分组和分类
• 与约束的结合使用
• 针对聚合结果的条件分支

2种方式

• 简单case表达式
• 搜索case表达式

-- 简单
case sex
	when '1' then '男'   -- then后面表示结果
	when '2' then '女'
else '其他' end

-- 搜索
case when sex='1' then '男'
		when sex='2' then '女'
else '其他' end

简单表达式能够写的搜索表达式都能写

注意事项

• 统一分支返回的数据类型
• 不要忘记写end
• 养成写else子句的习惯，虽然else子句是可选。如果不写，结果自动变成NULL

案例1-统计分组求和

需求

将表1的数据分组统计成表2，表1：

表2：

SQL实现

-- 县编号转成地区编号

-- 方式1
select case pref_name
        when '德岛' then '四国'
        when '香川' then '四国'
        when '爱媛' then '四国'
        when '高知' then '四国'

        when '福冈' then '九州'
        when '佐贺' then '九州'
        when '长崎' then '九州'
    else '其他' end as district,  -- 别名
    sum(population)
from poptable
group by case pref_name   -- 将case表达式的内容直接复制到这里，担任分组的元素
        when '德岛' then '四国'
        when '香川' then '四国'
        when '爱媛' then '四国'
        when '高知' then '四国'

        when '福冈' then '九州'
        when '佐贺' then '九州'
        when '长崎' then '九州'
    else '其他' end;

-- 方式2--->非常好用
select case pref_name
        when '德岛' then '四国'
        when '香川' then '四国'
        when '爱媛' then '四国'
        when '高知' then '四国'

        when '福冈' then '九州'
        when '佐贺' then '九州'
        when '长崎' then '九州'
    else '其他' end as district,  -- 别名
    sum(population)
from poptable
group by district;   -- 此处直接使用别名

在有些数据库是不支持这种写法，但是在mysql中是可以顺利执行的：

• 先对select子句里面的列表进行扫描
• 再对列进行计算

不推荐使用！！！！！

案例2-数字大小分类

表格同上

需求

将不同的数字按照类别进行分组表示

SQL实现

-- 按照人口数量等级划分
select case when population < 100 then '01'
						when population >=100 and population < 200  then '02'
						when population >=200 and population < 300  then '03'
						when population >=400 then '04'
				else NULL end as pop_class,
				count(*) as cnt
from poptable
group by case when population < 100 then '01'
						when population >=100 and population < 200  then '02'
						when population >=200 and population < 300  then '03'
						when population >=400 then '04'
				else NULL end;

案例3-不同条件统计(男女)

需求

将表1统计成表2：根据县的不同，来统计每个县的男女人数

SQL实现

比较传统的做法是直接通过2条SQL语句来实现

select pref_name,   -- 统计每个县的男性人数
		sum(population)
from poptable
where sex='1'
group by pref_name;

select pref_name,  -- 统计每个县的女性人数
		sum(population)
from poptable
where sex='2'
group by pref_name;

case实现：将行结构的数据变成了列结构的数据

select pref_name,
		-- 男性人口
		sum(case when sex='1' then population else 0 end) as cnt_m,
		sum(case when sex='2' then population else 0 end) as cnt_f
from poptable
group by pref_name

约束constraint

将case表达式和check约束结合起来，比如需求：女性员工的工资必须低于20万日元

constraint check_salary check (
    case when sex='2'  -- 指定女性
    then case when salary < 2000000
          then 1 -- 低于20万
          else 0 end
    else 1 end = 1)

逻辑与蕴含式

在蕴含式中，要想P--->Q为真，需要PQ同时为真，或者P为假，或者P的真假无法判定

案例4-update中使用case进行条件分支

需求

1．对当前工资为30万日元以上的员工，降薪10%。
2．对当前工资为25万日元以上且不满28万日元的员工，加薪20%

SQL实现

update salaries
		set salary = case when salary >= 300000 then salay * 0.9
											when salary >= 250000 and salary < 280000 then salary * 1.2
								 else salary end;   -- 不在上面的两个范围内保持不变，这个一定要写不然会变成NULL

使用case调换主键值

update someTable
		set p_key = case when p_key = 'a' then 'b'
										 when p_key = 'b' then 'a'
								else p_key end
		where p_key in ('a', 'b')

案例5-case嵌套子查询

需求

通过两张表生成第三张交叉表：

SQL实现

-- in 实现
select course_name,
	case when course_id in (
    		select course_id from opencourses
    		where month=200706) then 'o'  -- 6月份画o
   		else '*' end as '6月',
  case when course_id in (
    		select course_id from opencourses
    		where month=200707) then 'o'
   		else '*' end as '7月',
  case when course_id in (
    		select course_id from opencourses
    		where month=200808) then 'o'
    	else '*' as '8月'
from coursemaster

-- exists实现

select CM.course_name,
		case when exists (
      select course_id from OpenCourse OC
      where month = 200706
      and OC.course_id = CM.course_id)
    then 'o'
    else '*' end as "6月",

    case when exists (
      select course_id from OpenCourse OC
      where month = 200707
      and OC.course_id = CM.course_id)
    then 'o'
    else '*' end as "7月",

    case when exists (
      select course_id from OpenCourse OC
      where month = 200708
      and OC.course_id = CM.course_id)
    then 'o'
    else '*' end as "8月",
from CourseMaster CM;

特点

• 这样的查询方式没有进行聚合，不需要排序
• 从性能上说，exists更好

案例6-case中使用聚合函数

需求

关于学生社团的表格：

• 只参加一个社团的学生的社团ID
• 参加了多个社团的学生的主社团ID，Y表示是主社团**

SQL实现

-- 加入了一个社团
select std_id, max(club_id) as main_club
from StudentClub
group by std_id
having count(*) = 1;


-- 加入了多个社团的主ID
select std_id, club_id as main_club
from StudentClub
where main_club_flag='Y';  -- 加入了多个社团的主ID

Case 语句实现

select std_id,
			case when count(*) = 1    -- 只加入了一个社团
			then max(club_id)   -- 直接取出最大的ID号
			else max(case when main_club_flag = 'Y'   -- 指定加入了多个社团
              then club_id  -- 取出此时主社团对应的ID号
              else NULL end)   -- 一定要有else语句
      end as main_club
from StudentClub
group by std_id;

新手使用having进行分支，熟练之后可以使用select语句进行分支

总结

• case表达式可以写在select子句里时，可以写在聚合函数内部，也可以是外部
• 能够写列名和常量的位置，都可以使用case语句
• case表达式可以写在select、group by、having、where、order by子句中
• case表达式是一种表达式，而不是语句，具有更好的可移植性