|
方法一： select (case TitleOfCourtesy when 'Ms.' then '女士' when 'Mr.' then '男士' else '' end) as TitleOfCourtesy from dbo.Employees 方法二： select (case when TitleOfCourtesy = 'Ms.' then '女士' when TitleOfCourtesy = 'Mr.' then '男士' else '' end) as TitleOfCourtesy from dbo.Employees 假设数据库有一张表名为student的表。
如果现在要你根据这张表，查出江西省男女个数，广东省男生个数，浙江省男女个数 怎么写SQL语句？即要生成下结果表
答案 :select sex ,count ( case province when '广东省' then '广东省' end )as 广东省 ,count ( case province when '江西省' then '江西省' end )as 江西省 ,count ( case province when '浙江省' then '浙江省' end )as 浙江省 from student group by sex count()函数即根据给定的范围和group by(统计方式) 而统计行数据的条数
1. select sex from student (查询数据表中的存在的男女条数)
2.select sex, count (*) as num from student group by sex (查询表中男女数量)
3.select sex ,province, count (*)as num from student group by sex,province (查询各省男女数量)
4.select sex ,province, count (province)as num from student group by sex,province (查询各省男女数量) 结果跟上图一样：这说明换不换都一样。又有count (province)等价于 count(case province when '浙江省' then '浙江省' else province end ) 但是如果 缩小范围 count(case province when '浙江省' then '浙江省' end ) 那么 看下面 5.select sex ,province, count ( case province when '浙江省' then '浙江省' end )as num from student group by sex,province
即统计男女数量范围限定在浙江省 再精简一下即下面 6.select sex, count ( case province when '浙江省' then '浙江省' end ) as 浙江省 from student group by sex
已经接近我们的要求了，现在只要加上另几个字段就是了 7.select sex ,count ( case province when '广东省' then '广东省' end )as 广东省 ,count ( case province when '江西省' then '江西省' end )as 江西省 ,count ( case province when '浙江省' then '浙江省' end )as 浙江省 from student group by sex
case when then 知识点 (1) select (case province when '浙江省' then '浙江' when '江西省' then '江西' end ) as 省份 from student
如果默认范围如果没全包含则为空 像上图的广东省为空 (2)select (case province when '浙江省' then '浙江' when '江西省' then '江西' else province end ) as 省份 from student
(责任编辑：admin) |
