mySQL 的一些技巧 case __when

多行插入
insert into mdl_dz_user (id,nickname,realname,username,email)
select id,name as nickname,realname,email as username,email  from mdl_dz_admin_user where id<>1;

 

case。。 when

SELECT id, type,CASE WHEN type = 1 THEN 9 WHEN type = 2 THEN 7 WHEN type = 3 THEN 8 END sort  FROM mdl_dz_user ORDER BY sort
UPDATE mdl_dz_user as u1 INNER JOIN (SELECT * from mdl_dz_user where id =1 ) as u2 set u1.`password`= u2.password where u1.id = 1138;

在where 条件中添加 case_when

select profile_key,profile_value from node_profile where profile_key in ('sqr','ts_start','ts_end') and (case when profile_key='ts_start' then profile_value>'2013-12-19' end)

select personID,familyreg,address2,address1 from comperson where familyreg like '%'+(case when address2='' then substring(address1,1,2) else substring(address2,1,2) end )+'%'

检索条件的时候

SELECT count(case when type=2 then id end) as teacher,count(case when type=3 then id end) as student