mysql case when的用法是:1、用作簡單搜索,語法為【CASE [col_name] WHEN [value1]】;2、用作搜索函數(shù),語法為【CASE WHEN [expr] THEN [result1]】。
【相關(guān)學習推薦:mysql教程(視頻)】
mysql case when的用法是:
case when 的語法有兩種
簡單函數(shù)?
CASE?[col_name]?WHEN?[value1]?THEN?[result1]…ELSE?[default]?END
搜索函數(shù)?
CASE?WHEN?[expr]?THEN?[result1]…ELSE?[default]?END
這兩種語法有什么區(qū)別呢?
1、簡單函數(shù)
CASE?[col_name]?WHEN?[value1]?THEN?[result1]…ELSE?[default]?END
枚舉這個字段所有可能的值*
SELECT ????NAME?'英雄', ????CASE?NAME ????????WHEN?'德萊文'?THEN ????????????'斧子' ????????WHEN?'德瑪西亞-蓋倫'?THEN ????????????'大寶劍' ????????WHEN?'暗夜獵手-VN'?THEN ????????????'弩' ????????ELSE ????????????'無' ????END?'裝備' FROM ????user_info; 復制代碼 復制代碼 SELECT ????NAME?'英雄', ????CASE?NAME ????????WHEN?'德萊文'?THEN ????????????'斧子' ????????WHEN?'德瑪西亞-蓋倫'?THEN ????????????'大寶劍' ????????WHEN?'暗夜獵手-VN'?THEN ????????????'弩' ????????ELSE ????????????'無' ????END?'裝備' FROM ????user_info;
2、搜索函數(shù)
CASE?WHEN?[expr]?THEN?[result1]…ELSE?[default]?END
搜索函數(shù)可以寫判斷,并且搜索函數(shù)只會返回第一個符合條件的值,其他case被忽略
#?when?表達式中可以使用?and?連接條件 SELECT ????NAME?'英雄', ????age?'年齡', ????CASE ????????WHEN?age?=?30 ????????AND?age?<pre class="brush:php;toolbar:false">聚合函數(shù)?sum?配合?case?when?的簡單函數(shù)實現(xiàn)行轉(zhuǎn)列 SELECT ????st.stu_id?'學號', ????st.stu_name?'姓名', ????sum( ????????CASE?co.course_name ????????WHEN?'大學語文'?THEN ????????????sc.scores ????????ELSE ????????????0 ????????END ????)?'大學語文', ????sum( ????????CASE?co.course_name ????????WHEN?'新視野英語'?THEN ????????????sc.scores ????????ELSE ????????????0 ????????END ????)?'新視野英語', ????sum( ????????CASE?co.course_name ????????WHEN?'離散數(shù)學'?THEN ????????????sc.scores ????????ELSE ????????????0 ????????END ????)?'離散數(shù)學', ????sum( ????????CASE?co.course_name ????????WHEN?'概率論與數(shù)理統(tǒng)計'?THEN ????????????sc.scores ????????ELSE ????????????0 ????????END ????)?'概率論與數(shù)理統(tǒng)計', ????sum( ????????CASE?co.course_name ????????WHEN?'線性代數(shù)'?THEN ????????????sc.scores ????????ELSE ????????????0 ????????END ????)?'線性代數(shù)', ????sum( ????????CASE?co.course_name ????????WHEN?'高等數(shù)學'?THEN ????????????sc.scores ????????ELSE ????????????0 ????????END ????)?'高等數(shù)學' FROM ????edu_student?st LEFT?JOIN?edu_score?sc?ON?st.stu_id?=?sc.stu_id LEFT?JOIN?edu_courses?co?ON?co.course_no?=?sc.course_no GROUP?BY ????st.stu_id ORDER?BY ????NULL;
想了解更多編程學習,敬請關(guān)注mysql教程欄目!
? 版權(quán)聲明
文章版權(quán)歸作者所有,未經(jīng)允許請勿轉(zhuǎn)載。
THE END
喜歡就支持一下吧
相關(guān)推薦