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 是一個關(guān)鍵字你可以理解為語句的開始和結(jié)束。
when相當(dāng)于if做判斷，then就是判斷之后顯示的結(jié)果。如果成立顯示為1，不成立顯示為0
sum就是將各個值相加。形如：1+1+0+1+0+1+1+0+1+1
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 是一個關(guān)鍵字你可以理解為語句的開始和結(jié)束。
when相當(dāng)于if做判斷，then就是判斷之后顯示的結(jié)果。如果成立顯示為1，不成立顯示為0
sum就是將各個值相加。形如：1+1+0+1+0+1+1+0+1+1
<?php
//全鎮(zhèn)教師年齡分布，1為男，0為女
$sqljs = "select
sum(case when xingbie='男' then 1 else 0 end) as '男教師總?cè)藬?shù)',
sum(case when xingbie='女' then 1 else 0 end) as '女教師總?cè)藬?shù)',
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);
//釋放結(jié)果集
mysqli_free_result($resultjs);//教師男女年齡分布
//6.關(guān)閉數(shù)據(jù)庫資源
mysqli_close($conn);
?>信息科技 2020-04-12 21:33:39 通過 網(wǎng)頁 瀏覽(3372) 打印