黑马视频-SQL之case

tscoreId,

tsid,

tenglish,

评级=case

when tenglish>=95 then ‘优秀‘

when tenglish>=90 then ‘良好‘

when tenglish>=80 then ‘中‘

when tenglish is null then ‘缺考‘

else ‘以后的比尔盖茨‘

end

from TblScore

then 后面的数据后类型必须一致

是等值判断 
select 
   tscoreId,
   tsid,
   tenglish,
   评级= 
      case tenglish
                   when 100 then ‘一百分‘
                    when 90  then ‘九十分‘
            end
from TblScore

相当于switch

Create table Test1(
 A int,
 B int
)
insert into Test1(10,20)
insert into Test1(100,21)
 
select A,B,AOrB=
       case 
          when A>B then A
          else B
       end
from Test1

统计每个销售员的总销售金额，列出销售员名称，总销售金额、称号（>6000金牌；>5500银牌；...）select     销售员, 销售总金额=sum(销售数量*销售价格),
称号= case
    when sum(销售数量*销售价格)>6000 then ‘金牌销售员‘
    when sum(销售数量*销售价格)>5500 then ‘银牌销售员‘
    when sum(销售数量*销售价格)>4500 then ‘铜牌销售员‘
    else ‘普通销售员‘
end
from Ordergroup by 销售员order by 销售总金额 desc

create table Test1( number varchar(10),
amount int
)insert into Test(number ,amount) values (‘RK1‘，10)insert into Test(number ,amount) values (‘RK2‘，20)
insert into Test(number ,amount) values (‘RK3‘，-30)
insert into Test(number ,amount) values (‘RK4‘，-10)
正数为收入，负数支出
单号|收入|支出select   number,
收入=
case     when amount>0 then amount
end

来自为知笔记(Wiz)

黑马视频-SQL之case

标签：