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 ;
2、如果不希望列表中出现"sex"列，语句如下：
select tt.id,tt.name ,(case tt.sex when 1 then '男'
when 2 then '女'
else '空的'
end
)性别
from users tt ;
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;
解释: 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;
SqlServer mysql 按月统计所有部门案例:
以订单统计为例，前端展示柱状图（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
