SQL case when

jack    1
tom    2
anni    3
poly    4

 

 

select buyer_name,
(
case 
    when buyer_id = '1'
    then '东'
    when buyer_id = '2'
    then '南'
    when buyer_id = '3'
    then '西'
    when buyer_id = '4'
    then '北'
else
    '中'
end

--简单写法
--case buyer_id
--    when   '1' then '东'
--     when '2' then '南'
--    when '3' then '西'
--    when '4' then '北'
--else
--    '中'
--end

)as Position
from dbo.t_join_buyers

.

 

create table t_case_when
(
    zd varchar(20) 
)

insert into t_case_when values ('A001')
insert into t_case_when values ('B001')
insert into t_case_when values ('B002')
insert into t_case_when values ('B003')
insert into t_case_when values ('B004')
insert into t_case_when values ('C001')
insert into t_case_when values ('C002')
insert into t_case_when values ('D001')
insert into t_case_when values ('D002')

查询以ABCD开头的记录的数量

select 
(
case
    when zd like 'A%' then 'A'
    when zd like 'B%' then 'B'
    when zd like 'C%' then 'C'
    when zd like 'D%' then 'D'
else
    'F'
end
)
as 开头,count(zd) as 数量
from dbo.t_case_when
group by 
(
case
    when zd like 'A%' then 'A'
    when zd like 'B%' then 'B'
    when zd like 'C%' then 'C'
    when zd like 'D%' then 'D'
else
    'F'
end
)