sql 语句之 case
case语句语法：
--简单Case函数 CASE sex WHEN '1' THEN '男' WHEN '2' THEN '女' ELSE '其他' END
--Case搜索函数 CASE WHEN sex = '1' THEN '男' WHEN sex = '2' THEN '女' ELSE '其他' END
首先创建一张users表，其中包含id,name,sex三个字段，表内容如下： 复制代码 SQL> drop table users purge; drop table users purge ORA-00942: 表或视图不存在 SQL> create table users(id int,name varchar2(20),sex number); Table created SQL> insert into users(id,name) values(1,'张一'); 1 row inserted SQL> insert into users(id,name,sex) values(2,'张二',1); 1 row inserted SQL> insert into users(id,name) values(3,'张三'); 1 row inserted SQL> insert into users(id,name) values(4,'张四'); 1 row inserted SQL> insert into users(id,name,sex) values(5,'张五',2); 1 row inserted SQL> insert into users(id,name,sex) values(6,'张六',1); 1 row inserted SQL> insert into users(id,name,sex) values(7,'张七',2); 1 row inserted SQL> insert into users(id,name,sex) values(8,'张八',1); 1 row inserted SQL> commit; Commit complete SQL> select * from users; ID NAME SEX --------------------------------------- -------------------- ---------- 1 张一 2 张二 1 3 张三 4 张四 5 张五 2 6 张六 1 7 张七 2 8 张八 1 8 rows selected 复制代码 1、上表结果中的"sex"是用代码表示的，希望将代码用中文表示。可在语句中使用case语句： 复制代码 SQL> select u.id,u.name,u.sex, 2 (case u.sex 3 when 1 then '男' 4 when 2 then '女' 5 else '空的' 6 end 7 )性别 8 from users u; ID NAME SEX 性别 --------------------------------------- -------------------- ---------- ------ 1 张一 空的 2 张二 1 男 3 张三 空的 4 张四 空的 5 张五 2 女 6 张六 1 男 7 张七 2 女 8 张八 1 男 8 rows selected 复制代码 2、如果不希望列表中出现"sex"列，语句如下： 复制代码 SQL> select u.id,u.name, 2 (case u.sex 3 when 1 then '男' 4 when 2 then '女' 5 else '空的' 6 end 7 )性别 8 from users u; ID NAME 性别 --------------------------------------- -------------------- ------ 1 张一 空的 2 张二 男 3 张三 空的 4 张四 空的 5 张五 女 6 张六 男 7 张七 女 8 张八 男 8 rows selected 复制代码 3、将sum与case结合使用，可以实现分段统计。 如果现在希望将上表中各种性别的人数进行统计，sql语句如下： 复制代码 SQL> select 2 sum(case u.sex when 1 then 1 else 0 end)男性, 3 sum(case u.sex when 2 then 1 else 0 end)女性, 4 sum(case when u.sex <>1 and u.sex<>2 then 1 else 0 end)性别为空 5 from users u; 男性 女性 性别为空 ---------- ---------- ---------- 3 2 0 -------------------------------------------------------------------------------- SQL> select 2 count(case when u.sex=1 then 1 end)男性, 3 count(case when u.sex=2 then 1 end)女, 4 count(case when u.sex <>1 and u.sex<>2 then 1 end)性别为空 5 from users u; 男性 女 性别为空 ---------- ---------- ---------- 3 2 0
2.项目中实际应用
/** * 方法名称: findAucAgencyDealCount<br> * 描述： List 中的每一个对象都是一条记录<br> * Object[]中下标值依次是对应的字段列<br> * 执行原生的sql连接查询<br> * @param startDate * @param endDate * @return 数组集合 即 List<Object[]> */ @Query(value = "SELECT s.name as aucagencyName,sum(a.qty_auction) as aucLotCount," +"sum(case when b.is_deal=1 and a.is_published=2 then b.qty_deal else 0 end) as aucLotDealCount," +"sum(case when b.is_deal=1 and b.is_settled=1 and a.is_published=2 then b.qty_deal else 0 end) as factCount," +"sum(case when b.is_deal=4 and a.is_published=2 then b.qty_deal else 0 end) as regretCount," +"sum(case when a.is_published=2 and a.type=1 then 1 else 0 end) as shootnumber," +"sum(case when a.is_published=2 and a.type=2 then 1 else 0 end) as sellnumber," +"sum(case when a.is_published=2 and b.is_deal=1 and a.type=1 then 1 else 0 end) as aucLotDealnumber," +"sum(case when a.is_published=2 and b.is_deal=1 and a.type=1 and b.is_priority=1 then 1 else 0 end) as firstnumber," +"sum(case when a.is_published=2 and a.type=2 and b.is_deal=1 then 1 else 0 end) as sellDealnumber " +"from auc_lot a left join auc_brand b on a.id=b.auc_id left join sys_agency s on a.agency_id=s.id " +"where (DATE_FORMAT(a.published_time,'%Y-%m-%d') BETWEEN :startDate and :endDate) " + "and a.agency_id in (:agencyIds)" +"GROUP BY s.name",nativeQuery=true) List<Object> findAucAgencyDealCount(@Param("startDate") String startDate, @Param("endDate") String endDate,@Param("agencyIds") List<Long> agencyIds);
