mysql case when %查询_mysql,case when else查询

-- 1

select * from book ;

-- 2

select *,(

case id when '2' then '二'

else '保密' end) as number

from book ;

-- 3

select *,(

case id when '%2%' then '二' -- 只能使用== 不能使用like

else '保密' end) as number

from book ;

-------------------------------------------------------------

-- 4

select *,(case when id = 2 then '二'

when id = 3 then '三'

when id like '%4%' then '四' -- == 和 like均能使用

when id like '%5%' then '五'

when id like '%6%' then '六'

else '未知' end

) as number

from book;

-- 5

select *,(case when id like '%2%' then '二'

when id like '%3%' then '三'

when id like '%4%' then '四'

when id like '%5%' then '五'

when id like '%6%' then '六'

else '未知' end

) as number

from book;

从上往下依次查询的结果如下：

1

2

3

4

5

select *,(case when name like '%2%' then

case when name like '%d%' then 'd2'

when name like '%c%' then 'c2'

END

end

) as number1,

(case when name like '%6%' then

case when name like '%d%' then 'd6'

when name like '%c%' then 'c6'

when name like '%e%' then 'e6'

END

end

) as number2

from book;

select *,(case when name like '%2%' then

case when name like '%d%' then 'd2'

when name like '%c%' then 'c2'

END

when name like '%6%' then

case when name like '%d%' then 'd6'

when name like '%c%' then 'c6'

when name like '%e%' then 'e6'

END

end

) as number1

from book;