MySQL按年龄段查询

拿其中一句解释一下
sum(case when xingbie='男' and nianling >0 and nianling <30 then 1 else 0 end) as '男30以下',
先看sum的括号里面的部分
case when xingbie='男' and nianling >0 and nianling <30 then 1 else 0 end
它表示的含义是:如果性别为男,并且年龄在0-30岁之间成立为1,不成立为0.
case和end 是一个关键字你可以理解为语句的开始和结束。
when相当于if做判断,then就是判断之后显示的结果。如果成立显示为1,不成立显示为0
sum就是将各个值相加。形如:1+1+0+1+0+1+1+0+1+1
<?php

//全镇教师年龄分布,1为男,0为女

$sqljs = "select  
sum(case when xingbie='男' then 1 else 0 end) as '男教师总人数',
sum(case when xingbie='女' then 1 else 0 end) as '女教师总人数',
sum(case when xingbie='男'  and nianling >0 and nianling <30 then 1 else 0 end) as '男30以下',
sum(case when xingbie='女'  and nianling >0 and nianling <30 then 1 else 0 end) as '女30以下',
sum(case when xingbie='男'  and nianling >=30 and nianling <40 then 1 else 0 end) as '男30-40',
sum(case when xingbie='女'  and nianling >=30 and nianling <40 then 1 else 0 end) as '女30-40',
sum(case when xingbie='男'  and nianling >=40 and nianling <50 then 1 else 0 end) as '男40-50',
sum(case when xingbie='女'  and nianling >=40 and nianling <50 then 1 else 0 end) as '女40-50',
sum(case when xingbie='男'  and nianling >=50 then 1 else 0 end) as '男50以上',
sum(case when xingbie='女'  and nianling >=50 then 1 else 0 end) as '女50以上'
from  teacher  where zaizhi='是';";
$resultjs = mysqli_query($conn, $sqljs);
$rowsjs = mysqli_fetch_row($resultjs); 
//释放结果集
mysqli_free_result($resultjs);//教师男女年龄分布
    //6.关闭数据库资源
    mysqli_close($conn);

?>

技术 2020-04-12 21:33:39 通过 网页 浏览(766) 打印

共有0条评论!

发表评论