mysql case when的用法是什么

mysql case when的用法是:1、用作簡單搜索,語法為【CASE [col_name] WHEN [value1]】;2、用作搜索函數(shù),語法為【CASE WHEN [expr] THEN [result1]】。

mysql case when的用法是什么

【相關(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)聲明
THE END
喜歡就支持一下吧
點贊5 分享