sql case when then else end语句

语句"/>

sql case when then else end语句

SELECT NAME as '姓名',
(CASEWHEN score >= 90 THEN'优' ELSE '差' 
END) as '等级' 
FROMstu

批量更新数据

 <!--批量更新数据--><update id="updatePsnInfoBBatch" parameterType="java.util.List">update psn_info_b<trim prefix="set"><trim prefix="psn_name = case" suffix="end,"><foreach collection="list" item="item"><if test="item.psnName!=null">when psn_no = #{item.psnNo,jdbcType=VARCHAR} then #{item.psnName,jdbcType=VARCHAR}</if></foreach></trim><trim prefix="certno = case" suffix="end"><foreach collection="list" item="item"><if test="item.certno!=null">when psn_no = #{item.psnNo,jdbcType=VARCHAR} then #{item.certno,jdbcType=VARCHAR}</if></foreach></trim></trim><where><foreach collection="list" item="item" separator="or"><trim prefix="(" suffix=")">psn_no = #{item.psnNo,jdbcType=VARCHAR}</trim></foreach></where></update>

case ... when ... then ... when ... then ...end

类似与java中的

 switch ... case

表结构及数据

sql语句

-- CASE sex WHEN 1 THEN 1 ELSE 0 END  
-- 当sex的值为1时，返回1，否则返回0,将返回的值相加；即男性人数SELECTSUM(CASE sex WHEN 1 THEN 1 ELSE 0 END ) 男人数,SUM( CASE sex WHEN 0 THEN 1 ELSE 0 END ) 女人数
FROM`user`

结果

SELECTsid,subject_id,subject_score,CASEWHEN subject_score >= 60 THEN'pass' ELSE 'fail' END mark
FROMscore