----case------ --说明:当我们在查询的时候,需要显示的不是表中列的值,而是根据列值显示其他值 --(1)区间判断 ----select -- 要显示的自定义列名 -- case when 条件判断 then 需要显示的值 -- when... -- else... -- end -- (2)等级判断 --select ---case 列名 -- when 列值 then 需要显示的值 -- else 需要显示的值 -- end select * from BBSUsers select [uId], name, 等级=[level] from BBSUsers -----------等级判断----------- select [uId], name, 等级= case [level] when 1 then ‘下官‘ when 2 then ‘下士‘ when 3 then ‘大兵‘ end from BBSUsers --------------区间判断---------------- select * from TblScore select tScoreId, tSId, 总分=tEnglish+tMath, 等级= case when tEnglish+tMath>180 then‘优秀‘ when tEnglish+tMath>160 then ‘良好‘ when tEnglish+tMath>120 then ‘一般‘ else ‘不及格‘ --when tEnglish+tMath<120 then ‘不及格‘ end from TblScore --当使用case 的时候要求then后面的数据类型必须一致 ------------------------------------------- select * from MyOrders select 销售员, 销售总价=sum(销售数量*销售价格), 等级= case when sum(销售数量*销售价格)>6000 then ‘金牌‘ when sum(销售数量*销售价格)>5500 then ‘银牌‘ end from MyOrders group by 销售员 ------------------------------------------- select * from CaseTest1 select 单号=number, 收入= case when amount>0 then amount else 0 end, 支出= case when amount<0 then abs(amount) else 0 end from CaseTest1 ------------------------- --(1) select * from TeamScore select teamName, 胜= count( case when gameresult=‘负‘ then null else gameResult end), 负= count( case when gameresult=‘胜‘ then null else gameResult end) from TeamScore group by teamName ---------- --(2) select teamName, 胜= SUM( case when gameresult=‘胜‘ then 1 else 0 end), 负= SUM( case when gameresult=‘负‘ then 1 else 0 end) from TeamScore group by teamName
select * from NBAScore --(1) select teamName, 第1赛季= SUM( case when seasonName=‘第1赛季‘ then Score end), 第2赛季= SUM( case when seasonName=‘第2赛季‘ then Score end), 第3赛季= SUM( case when seasonName=‘第3赛季‘ then Score end) from NBAScore group by teamName --(2) select teamName, 第1赛季= MAX( case when seasonName=‘第1赛季‘ then Score end), 第2赛季= MAX( case when seasonName=‘第2赛季‘ then Score end), 第3赛季= MAX( case when seasonName=‘第3赛季‘ then score end) from NBAScore group by teamName --------------------------------------------------------------- select * from StudentScore select studentId, 语文= SUM( case when courseName=‘语文‘ then score end), 数学= SUM( case when courseName=‘数学‘ then score end), 英语= SUM( case when courseName=‘英语‘ then score end) from StudentScore group by studentId ---------------------------------------------------------------- select * from MyOrders select 商品编号, 商品名称, 王大销售数量= SUM( case when 销售员=‘王大‘ then 销售数量 end), 刘七销售数量= SUM( case when 销售员=‘刘七‘ then 销售数量 end), 张三销售数量= SUM( case when 销售员=‘张三‘ then 销售数量 end), 李四销售数量= SUM( case when 销售员=‘李四‘ then 销售数量 end), 赵五销售数量= SUM( case when 销售员=‘赵五‘ then 销售数量 end) from MyOrders group by 商品编号,商品名称 ---------------------------------------------------