# SQL 各班男女的BMI平均數

1. ### cwhk Expand Collapse

文章:
40
讚:
0
討論區已有選取BMI的SQL，但ESDA要求各班男女的BMI平均數，請問如何修改SQL？謝謝。

2. ### edb-catherinewschan Expand Collapse

文章:
302
讚:
0
請問BMI 在WEBSAMS那裡輸入呢??

#2 edb-catherinewschan, 2020-07-14
3. ### cwhk Expand Collapse

文章:
40
讚:
0
不明白你的意思，學校輸入學生身高、體重的資料，然後用SQL抽取BMI，但ESDA要求各班男女的BMI平均數，所以問如何修改本來在這討論區的SQL。

4. ### edb-catherinewschan Expand Collapse

文章:
302
讚:
0
請給我SQL 看看

#4 edb-catherinewschan, 2020-07-15
5. ### cwhk Expand Collapse

文章:
40
讚:
0
select
b.CLASSCODE,
b.CLASSNO,
a.CHNAME,
a.ENNAME,
dateformat(c.MEASUREDATE,'dd/mm/yyyy') 'MEASUREDATE_first',
e.HEIGHT 'HEIGHT_first',
e.WEIGHT 'WEIGHT_first',
ifnull(e.HEIGHT, null,
(case when e.HEIGHT=0 then '-'
else string(convert(numeric(10,2),e.WEIGHT/(power(e.HEIGHT,2)/10000.0)))
end)) 'BMI_first',
dateformat(d.MEASUREDATE,'dd/mm/yyyy') 'MEASUREDATE_last',
f.HEIGHT 'HEIGHT_last',
f.WEIGHT 'WEIGHT_last',
ifnull(f.HEIGHT, null,
(case when f.HEIGHT=0 then '-'
else string(convert(numeric(10,2),f.WEIGHT/(power(f.HEIGHT,2)/10000.0)))
end)) 'BMI_last'
on a.SUID = b.SUID and a.STUID = b.STUID and b.SCHYEAR = ?
left outer join
(select SUID, STUID, SCHYEAR, min(MEASUREDATE) 'MEASUREDATE'
group by SUID, STUID, SCHYEAR) c
on a.SUID = c.SUID and a.STUID = c.STUID and b.SCHYEAR = c.SCHYEAR
on a.SUID = e.SUID and a.STUID = e.STUID and b.SCHYEAR = e.SCHYEAR and
c.MEASUREDATE = e.MEASUREDATE
left outer join
(select SUID, STUID, SCHYEAR, max(MEASUREDATE) 'MEASUREDATE'
group by SUID, STUID, SCHYEAR) d
on a.SUID = d.SUID and a.STUID = d.STUID and b.SCHYEAR = d.SCHYEAR
on a.SUID = f.SUID and a.STUID = f.STUID and b.SCHYEAR = f.SCHYEAR and
d.MEASUREDATE = f.MEASUREDATE
order by b.SCHSESS, b.CLASSLVL, b.CLASSCODE, b.CLASSNO

6. ### edb-catherinewschan Expand Collapse

文章:
302
讚:
0
但是用那個FIELD 去計BMI AVERAGE ?? 'BMI_first' 或 'BMI_last' ?
我估計可以先加上學生性別欄位, 再計男女的BMI AVERAGE

select
b.CLASSCODE,
b.CLASSNO,
a.CHNAME,
a.ENNAME,
a.sex,
dateformat(c.MEASUREDATE,'dd/mm/yyyy') 'MEASUREDATE_first',
e.HEIGHT 'HEIGHT_first',
e.WEIGHT 'WEIGHT_first',
ifnull(e.HEIGHT, null,
(case when e.HEIGHT=0 then '-'
else string(convert(numeric(10,2),e.WEIGHT/(power(e.HEIGHT,2)/10000.0)))
end)) 'BMI_first',
dateformat(d.MEASUREDATE,'dd/mm/yyyy') 'MEASUREDATE_last',
f.HEIGHT 'HEIGHT_last',
f.WEIGHT 'WEIGHT_last',
ifnull(f.HEIGHT, null,
(case when f.HEIGHT=0 then '-'
else string(convert(numeric(10,2),f.WEIGHT/(power(f.HEIGHT,2)/10000.0)))
end)) 'BMI_last'
on a.SUID = b.SUID and a.STUID = b.STUID and b.SCHYEAR = ?
left outer join
(select SUID, STUID, SCHYEAR, min(MEASUREDATE) 'MEASUREDATE'
group by SUID, STUID, SCHYEAR) c
on a.SUID = c.SUID and a.STUID = c.STUID and b.SCHYEAR = c.SCHYEAR
on a.SUID = e.SUID and a.STUID = e.STUID and b.SCHYEAR = e.SCHYEAR and
c.MEASUREDATE = e.MEASUREDATE
left outer join
(select SUID, STUID, SCHYEAR, max(MEASUREDATE) 'MEASUREDATE'
group by SUID, STUID, SCHYEAR) d
on a.SUID = d.SUID and a.STUID = d.STUID and b.SCHYEAR = d.SCHYEAR
on a.SUID = f.SUID and a.STUID = f.STUID and b.SCHYEAR = f.SCHYEAR and
d.MEASUREDATE = f.MEASUREDATE
order by b.SCHSESS, b.CLASSLVL, b.CLASSCODE, b.CLASSNO

#6 edb-catherinewschan, 2020-07-17
7. ### cwhk Expand Collapse

文章:
40
讚:
0
ESDA要求各班男女的BMI平均數，即男的BMI相加，女的BMI相加，然後各自求各班的平均數。討論區的只可取出各人的BMI，可否修改SQL，達致ESDA的要求？謝謝。

8. ### edb-catherinewschan Expand Collapse

文章:
302
讚:
0
你好,

用以下的SQL是可以進一步計算到男女的BMI平均值, 但可惜WEBSAMS不支援這樣的語法。

1, 用WEBSAMS也些REPORT, 你看看以下的, 查看有沒有你所需的資料。
學生的體格發展統計報告 (R-STU075-C)
身高及體重報告 (R-STU207-C)

特別附上一個基礎成品 BMI.rpt , 你可以在WEBSAMS的上載範本以R-STU75-C的規格放上去試行。

select
T.CLASSCODE,
T.sex,
avg(T.BMI_first) as avg_BMIfirst,
avg(T.BMI_last) as avg_BMIlast
from
(
select
b.CLASSCODE,
b.CLASSNO,
a.CHNAME,
a.ENNAME,
a.sex,
dateformat(c.MEASUREDATE,'dd/mm/yyyy') 'MEASUREDATE_first',
e.HEIGHT 'HEIGHT_first',
e.WEIGHT 'WEIGHT_first',
ifnull(e.HEIGHT, null,
(case when e.HEIGHT=0 then '-'
else string(convert(numeric(10,2),e.WEIGHT/(power(e.HEIGHT,2)/10000.0)))
end)) 'BMI_first',
dateformat(d.MEASUREDATE,'dd/mm/yyyy') 'MEASUREDATE_last',
f.HEIGHT 'HEIGHT_last',
f.WEIGHT 'WEIGHT_last',
ifnull(f.HEIGHT, null,
(case when f.HEIGHT=0 then '-'
else string(convert(numeric(10,2),f.WEIGHT/(power(f.HEIGHT,2)/10000.0)))
end)) 'BMI_last'
on a.SUID = b.SUID and a.STUID = b.STUID and b.SCHYEAR = ?
left outer join
(select SUID, STUID, SCHYEAR, min(MEASUREDATE) 'MEASUREDATE'
group by SUID, STUID, SCHYEAR) c
on a.SUID = c.SUID and a.STUID = c.STUID and b.SCHYEAR = c.SCHYEAR
on a.SUID = e.SUID and a.STUID = e.STUID and b.SCHYEAR = e.SCHYEAR and
c.MEASUREDATE = e.MEASUREDATE
left outer join
(select SUID, STUID, SCHYEAR, max(MEASUREDATE) 'MEASUREDATE'
group by SUID, STUID, SCHYEAR) d
on a.SUID = d.SUID and a.STUID = d.STUID and b.SCHYEAR = d.SCHYEAR
on a.SUID = f.SUID and a.STUID = f.STUID and b.SCHYEAR = f.SCHYEAR and
d.MEASUREDATE = f.MEASUREDATE
order by b.SCHSESS, b.CLASSLVL, b.CLASSCODE, b.CLASSNO
) T
group by T.classcode, T.sex
order by T.classcode, T.sex

#### 附件文件:

• ###### BMI.zip
文件大小:
10.2 KB
瀏覽:
177
#8 edb-catherinewschan, 2020-07-22