SQL之case when then用法

 

创建表

create table users(id int,name varchar2(20),sex number);
 
insert into users(id,name) values(1,‘张一‘);
insert into users(id,name,sex) values(2,‘张二‘,1);
insert into users(id,name) values(3,‘张三‘);
insert into users(id,name) values(4,‘张四‘);
insert into users(id,name,sex) values(5,‘张五‘,2);
insert into users(id,name,sex) values(6,‘张六‘,1);
insert into users(id,name,sex) values(7,‘张七‘,2);
insert into users(id,name,sex) values(8,‘张八‘,1);
 
commit;

1、上表结果中的"sex"是用代码表示的,希望将代码用中文表示。可在语句中使用case语句:

select * from users;
select tt.* ,(case tt.sex when 1 then ‘男‘
                      when 2 then ‘女‘
                      else ‘空的‘
                      end
                      )性别
           from users tt ;

SQL之case when then用法

2、如果不希望列表中出现"sex"列,语句如下:

select tt.id,tt.name ,(case tt.sex when 1 then ‘男‘
                      when 2 then ‘女‘
                      else ‘空的‘
                      end
                      )性别
           from users tt ;

 SQL之case when then用法

3、将sum与case结合使用,可以实现分段统计。

select sum(case when tt.sex = 1 then 1 else 0 end)男性,
       sum(case  when tt.sex = 2 then 1 else 0 end)女性,
       sum(case when tt.sex <>1 and tt.sex <>1 then 1 else 0 end)性别为空
       from users tt;

        男性         女性       性别为空
---------- ---------- ----------
         3          2          0
       select count(case when tt.sex = 1 then 1  end)男性,
       count(case  when tt.sex = 2 then 1  end)女性,
       count(case when tt.sex <>1 and tt.sex <>1 then 1  end)性别为空
       from users tt;

3.1、这里发现了一个错误 性别为空的为0 why? 不是3吗?

 t-sql 三个用于查询的筛选器
on  第一个
where  第二个
having 第三个

在SQL中,逻辑表达式的可能值为True False Unknown,称为三值逻辑
当比较丢失值和另一个值,逻辑结果为Unknown,例如:null>42
所有的筛选器都把Unknown 当做Fasle 处理
但是Check 约束会把Unknown当做True处理
而唯一约束,排序操作和分组操作认为两个Null值是相等的
如果表中有一列定义了唯一约束,将无法向表内插入该值为Null的两行
Group by 子句把所有Null值分到一组
Order by 子句把所有Null值排列在一起.


(2)应用ON筛选器(联接条件) 说到这里,首先得说下 SQL用于查询的有三个筛选器,分别是ON,where,having。 这里的ON筛选器就是用VTB1作为输入,再利用ON后面的联结条件进行筛选,生成VTB2。 sql编程第二特征:三值逻辑:它其实就是所谓的TRUE FALSE UNKOWN 主要来说这个UNKOWN 它这逻辑值和NULL在一起就会”出事“。凡是NULL参与的比如 NULL>42, NULL-8>9 这些结果都是UNKOWN UNKOWN 的逻辑结果在不同的环境下是被不同的方式处理的:如 在三个筛选器上,它是会被当做FALSE处理; 在CHECK约束上是会被当成TRUE来处理,check(col>8) 你插入的COL为NULL,因为NULL>8 的结果不就是UNKOWN ,所以是可以插入的。 在筛选器上的2个NULL比较将是FALSE 但是在UNIQUE约束,分组,排序上2个NULL是等价的。  
算术运算
如果算术表达式的任一输入为空,则该算术表达式(涉及诸如 +、-、* 或 / 的算术运算)结果为空。

例:如果查询中有一个表达式是r.A + 5, 并且对于关系中某个特定的元组, r.A为空,那么对此元组来说,该表达式的结果也为空。

比较运算
SQL将涉及空值的任何比较运算的结果视为unknown(既不是谓词is null,也不是is not null)。unknown是在SQL中除true和false之外的第三个逻辑值。

补充:由于在where子句的谓词中可以对比较结果使用诸如and、or和not的布尔运算,将布尔运算扩展到可以处理unknown值,运算结果如下:

and:true and unknown = unknown, false and unknown = false, unknown and unknown = unknown
or:true or unknown = true, false or unknown = unknown, unknown or unknown = unknown
not:not unknown = unknown
可以验证,如果r.A为空,那么"1 < r.A" 和 "not (1 < r.A)"的结果都为unknown。

"null = null"会返回unknown,而不是true。

 

select  (case when tt.sex <>1 and tt.sex <>2 then 1  end)性别为空
       from users tt;

  

SQL之case when then用法

解释: sex=1 时候 不符合条件 1<>1 ,sex=2 时候 2<>2 不符合条件,为null的时候 unknown 筛选器当做false处理,所以,都不符合条件,不能设置为1,设置为null.

  

这里就能解释了 

       select  count(case when tt.sex <>1 and tt.sex <>2 then 1  end)性别为空
       from users tt; //结果为0

 解释: tt.sex <>1  为null<>1 为unkown  , unknown and unknown 为unkonwn 被选择器when 当做fasle处理,被筛下丢弃,所以结果为0

       select  (case when tt.sex <>1 and tt.sex <>2 then 1  end)性别为空
       from users tt;

 所以最后对null的判断不对,改为:select count(case when tt.sex is null then 1 end)性别为空 from users tt;

以订单统计为例,前端展示柱状图(Jquery统计):

表及主要字段描述如下;表名:Orders
1.日期CreateTime
2.金额Amount
3.用户UserID

情况一:
根据部门统计某一年每月销量(查询一个部门月统计)

1)直接在SQL语句中判断每月信息,好处,前台直接调用;坏处,性能不高。

SQL语句:

SELECT 
SUM(CASE WHEN MONTH(s.CreateTime) = 1 THEN s.Amount ELSE 0 END) AS ‘一月‘,
SUM(CASE WHEN MONTH(s.CreateTime) = 2 THEN s.Amount ELSE 0 END) AS ‘二月‘,
SUM(CASE WHEN MONTH(s.CreateTime) = 3 THEN s.Amount ELSE 0 END) AS ‘三月‘,
SUM(CASE WHEN MONTH(s.CreateTime) = 4 THEN s.Amount ELSE 0 END) AS ‘四月‘,
SUM(CASE WHEN MONTH(s.CreateTime) = 5 THEN s.Amount ELSE 0 END) AS ‘五月‘,
SUM(CASE WHEN MONTH(s.CreateTime) = 6 THEN s.Amount ELSE 0 END) AS ‘六月‘,
SUM(CASE WHEN MONTH(s.CreateTime) = 7 THEN s.Amount ELSE 0 END) AS ‘七月‘,
SUM(CASE WHEN MONTH(s.CreateTime) = 8 THEN s.Amount ELSE 0 END) AS ‘八月‘,
SUM(CASE WHEN MONTH(s.CreateTime) = 9 THEN s.Amount ELSE 0 END) AS ‘九月‘,
SUM(CASE WHEN MONTH(s.CreateTime) = 10 THEN s.Amount ELSE 0 END) AS ‘十月‘,
SUM(CASE WHEN MONTH(s.CreateTime) = 11 THEN s.Amount ELSE 0 END) AS ‘十一月‘,
SUM(CASE WHEN MONTH(s.CreateTime) = 12 THEN s.Amount ELSE 0 END) AS ‘十二月‘
FROM Orders AS s
WHERE YEAR(s.CreateTime) = 2014 

 

一月    二月    三月    四月    五月    六月    七月    八月    九月    十月    十一月    十二月
0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    741327.00    120505.00    0.00

2)统计出数据库里有值的月份,再前端逻辑判断其他月份补0

SQL语句:

SELECT UserID, MONTH(CreateTime) as 月份, SUM(Amount) as 统计
  FROM Orders
 WHERE YEAR(CreateTime) = 2014 -- 这里假设你要查 2014年的每月的统计。
--其他条件
 GROUP BY UserID, MONTH(CreateTime)

  

参考:https://www.cnblogs.com/qlqwjy/p/7476533.html  

atzhang 

SQL之case when then用法

上一篇:oracle 12c 关闭统计信息收集和启用统计信息收集


下一篇:WIN10 ,Windows Server 2012R2,oracle11安装问题