数据库中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	"张八"	"男"