语句"/>
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
更多推荐
sql case when then else end语句
发布评论