17.Case函数

     严格意义上来讲CASE函数已经是流程控制语句了，不是简单意义上的函数，不过为了方便，很多人都将CASE称作“流程控制函数”。Case函数只返回第一个符合条件的值，剩下的Case部分将会被自动忽略。 

简单 CASE 函数：

     将某个表达式与一组简单表达式进行比较以确定结果。

case expression 
    when value1 then returnvalue1 
    when value2 then returnvalue2 
    when value3 then returnvalue3 
       ... 
else defaultreturnvalue 
end 

     CASE函数对表达式expression进行测试，如果expression等于value1则返回returnvalue1，如果expression等于value2则返回returnvalue2，expression等于value3则返回returnvalue3，……以此类推，如果不符合所有的WHEN条件，则返回默认值defaultreturnvalue。改用法用法只能用来实现简单的“等于”逻辑的判断，可见CASE函数和普通编程语言中的SWITCH……CASE语句非常类似。

SELECT  
  FName, 
 (
   case FName  
      when 'Tom' then 'GoodBoy'  
      when 'Lily' then 'GoodGirl'  
      when 'Sam' then 'BadBoy'  
      when  'Kerry' then 'BadGirl' 
   else 'Normal'  
   end
 ) as isgood 
FROM T_Person 

CASE搜索函数：

case 
    when condition1 then returnvalue1 
    when condition2 then returnvalue2 
    when condition3 then returnvalue3 
    ...
else defaultreturnvalue 
end 

     其中的condition1 、condition 2、condition 3……为条件表达式，CASE函数对各个表达式从前向后进行测试，如果条件condition1为真则返回returnvalue1，否则如果条件condition2为真则返回returnvalue2，否则如果条件condition3为真则返回returnvalue3，……以此类推，如果不符合所有的WHEN条件，则返回默认值defaultreturnvalue。

SELECT  
  FName, 
  FWeight, 
 (
    case 
       where FWeight<40 then 'thin'  
       where FWeight>50 then 'fat'  
    else 'ok'  
    end
 ) as isnormal 
FROM T_Person 

示例1：已知数据按照另外一种方式进行分组

create table Ttemp
(
    country varchar(10),
    [population] int
)

insert into Ttemp(country,[population]) values ('中国',600)
insert into Ttemp(country,[population]) values ('美国',100)
insert into Ttemp(country,[population]) values ('加拿大',100)
insert into Ttemp(country,[population]) values ('英国',200)
insert into Ttemp(country,[population]) values ('法国',300)
insert into Ttemp(country,[population]) values ('日本',250)
insert into Ttemp(country,[population]) values ('德国',200)
insert into Ttemp(country,[population]) values ('墨西哥',50)
insert into Ttemp(country,[population]) values ('印度',250)

select case country
          when '中国'  then '亚洲'
          when '印度'  then '亚洲'
          when '日本'  then '亚洲'
          when '美国'  then '北美洲'
          when '加拿大'  then '北美洲'
          when '墨西哥'  then '北美洲'
        else '其他'
        end,
        sum([population])
from Ttemp
group by case country
          when '中国'  then '亚洲'
          when '印度'  then '亚洲'
          when '日本'  then '亚洲'
          when '美国'  then '北美洲'
          when '加拿大'  then '北美洲'
          when '墨西哥'  then '北美洲'
        else '其他'
        end       
        
drop table Ttemp

     

     下面使用case搜索函数来判断工资的等级，并统计每一等级的人数。

SELECT 
        CASE WHEN salary <= 500 THEN '1' 
             WHEN salary > 500 AND salary <= 600  THEN '2' 
             WHEN salary > 600 AND salary <= 800  THEN '3' 
             WHEN salary > 800 AND salary <= 1000 THEN '4' 
        ELSE NULL 
        END salary_class, 
        COUNT(*) 
FROM    Table_A 
GROUP BY 
        CASE WHEN salary <= 500 THEN '1' 
             WHEN salary > 500 AND salary <= 600  THEN '2' 
             WHEN salary > 600 AND salary <= 800  THEN '3' 
             WHEN salary > 800 AND salary <= 1000 THEN '4' 
        ELSE NULL 
        END 

示例2：用一个SQL语句完成不同条件的分组

create table Ttemp
(
    country varchar(10),
    sex bit,
    [population] int
)

insert into Ttemp(country,sex,[population]) values ('中国',0,340)
insert into Ttemp(country,sex,[population]) values ('中国',1,260)
insert into Ttemp(country,sex,[population]) values ('美国',0,45)
insert into Ttemp(country,sex,[population]) values ('美国',1,55)
insert into Ttemp(country,sex,[population]) values ('加拿大',0,51)
insert into Ttemp(country,sex,[population]) values ('加拿大',1,49)
insert into Ttemp(country,sex,[population]) values ('英国',0,40)
insert into Ttemp(country,sex,[population]) values ('英国',1,60)


SELECT country, 
       SUM( CASE WHEN sex = '0' THEN population ELSE 0 END) 男性人口, 
       SUM( CASE WHEN sex = '1' THEN population ELSE 0 END) 女性人口  
FROM  Ttemp 
GROUP BY country
  
        
drop table Ttemp

 示例3：按条件对数据进行更新

UPDATE Personnel
SET salary = 
      CASE 
        WHEN salary >= 5000  THEN salary * 0.9
        WHEN salary >= 2000 AND salary < 4600 THEN salary * 1.15
      ELSE salary 
      END

示例4：在CASE中使用Null

declare @para varchar

select
    case @para
       when null then 'null'
       when '1' then '1'
    else ''
    end 

     在这个语句中When Null这一行总是返回unknown，所以永远不会出现Wrong的情况。因为这句可以替换成WHEN col_1 = NULL，这是一个错误的用法，这个时候我们应该选择用WHEN col_1 IS NULL。

declare @para varchar
set @para='1'

select
    case 
       when @para is null then 'null'
       when  @para='1' then '1'
    end 

 

三层树状结构表取结构数据

 select 
   case  
    when provinceName is null and cityName is null and projectName is null then ''
    when provinceName is null and cityName is null and projectName is not null then projectName
    when provinceName is null and cityName is not null and projectName is not null then cityName+'>'+projectName
    else provinceName+'>'+cityName+'>'+projectName
   end
 from
 ( 
  select provinceProject.name provinceName,cityProject.name cityName,project.name projectName 
  from project
  left join project cityProject on project.parentId=cityProject.projectId
  left join project provinceProject on cityProject.parentId=provinceProject.projectId
  where project.projectId='703E2AAC-3C7A-4CDD-AB89-2BF4EDACDD48'
 ) as T