数据库中case when condition then else end的理解

数据库中case when condition then else end的理解

• 数据库中根据不同的条件求数量或者是总数

  select count(case when status=0 then 'done' end)as done,count(case when status=-1 then 'doing' end)as doing from t_func;

  • when 表示条件，then value end：表示符合条件则在对应赋值为value
  • count 计算所有有值的情况，会自动滤过null值

• select (case column when condition then value else value) from tablename 中可以选值分类

  • 例子：

create table t_users (id int,name varchar(20),sex int);
insert into t_users(id,name) values(1,'张一');
insert into t_users(id,name,sex) values(2,'张二',1);
insert into t_users(id,name) values(3,'张三');
insert into t_users(id,name) values(4,'张四');
insert into t_users(id,name,sex) values(5,'张五',2);
insert into t_users(id,name,sex) values(6,'张六',1);
insert into t_users(id,name,sex) values(7,'张七',2);
insert into t_users(id,name,sex) values(8,'张八',1);
select * from t_users;
select id,name,(case when sex=1 then '男' when sex=2 then '女' else '空的' end) 性别 from t_users

• t_users表内容：

  1 "张一"   [null]
  2 "张二"    1
  3 "张三"   [null]
  4 "张四"   [null]
  5 "张五"    2
  6 "张六"    1
  7 "张七"    2
  8 "张八"    1

• 查询结果：

  1 "张一"    "空的"
  2 "张二"    "男"
  3 "张三"    "空的"
  4 "张四"    "空的"
  5 "张五"    "女"
  6 "张六"    "男"
  7 "张七"    "女"
  8 "张八"    "男"