SQL 各班男女的BMI平均數

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

 

請問BMI 在WEBSAMS那裡輸入呢??

 

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

 

請給我SQL 看看

 

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'
from wsadmin.TB_STU_STUDENT a
join wsadmin.VW_STU_LATESTSTUSCHREC b
on a.SUID = b.SUID and a.STUID = b.STUID and b.SCHYEAR = ?
left outer join
(select SUID, STUID, SCHYEAR, min(MEASUREDATE) 'MEASUREDATE'
from wsadmin.TB_STU_HEIGHTWEIGHT
group by SUID, STUID, SCHYEAR) c
on a.SUID = c.SUID and a.STUID = c.STUID and b.SCHYEAR = c.SCHYEAR
left outer join wsadmin.TB_STU_HEIGHTWEIGHT e
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'
from wsadmin.TB_STU_HEIGHTWEIGHT
group by SUID, STUID, SCHYEAR) d
on a.SUID = d.SUID and a.STUID = d.STUID and b.SCHYEAR = d.SCHYEAR
left outer join wsadmin.TB_STU_HEIGHTWEIGHT f
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


https://forum.hkedcity.net/index.php?threads/學生身高體重.43332/#post-182562

 

但是用那個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'
from wsadmin.TB_STU_STUDENT a
join wsadmin.VW_STU_LATESTSTUSCHREC b
on a.SUID = b.SUID and a.STUID = b.STUID and b.SCHYEAR = ?
left outer join
(select SUID, STUID, SCHYEAR, min(MEASUREDATE) 'MEASUREDATE'
from wsadmin.TB_STU_HEIGHTWEIGHT
group by SUID, STUID, SCHYEAR) c
on a.SUID = c.SUID and a.STUID = c.STUID and b.SCHYEAR = c.SCHYEAR
left outer join wsadmin.TB_STU_HEIGHTWEIGHT e
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'
from wsadmin.TB_STU_HEIGHTWEIGHT
group by SUID, STUID, SCHYEAR) d
on a.SUID = d.SUID and a.STUID = d.STUID and b.SCHYEAR = d.SCHYEAR
left outer join wsadmin.TB_STU_HEIGHTWEIGHT f
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

 

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

 

你好,

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

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

2, 另外，你可以建造一個空白的CRYSTAL REPORT並加插以下的SQL , 參照範本R-STU75-C的參數, 造一個新的報告列印出來。 這些技巧在CRYSTAL REPORT ADVANCE 課程有教的。

特別附上一個基礎成品 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'
from wsadmin.TB_STU_STUDENT a
join wsadmin.VW_STU_LATESTSTUSCHREC b
on a.SUID = b.SUID and a.STUID = b.STUID and b.SCHYEAR = ?
left outer join
(select SUID, STUID, SCHYEAR, min(MEASUREDATE) 'MEASUREDATE'
from wsadmin.TB_STU_HEIGHTWEIGHT
group by SUID, STUID, SCHYEAR) c
on a.SUID = c.SUID and a.STUID = c.STUID and b.SCHYEAR = c.SCHYEAR
left outer join wsadmin.TB_STU_HEIGHTWEIGHT e
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'
from wsadmin.TB_STU_HEIGHTWEIGHT
group by SUID, STUID, SCHYEAR) d
on a.SUID = d.SUID and a.STUID = d.STUID and b.SCHYEAR = d.SCHYEAR
left outer join wsadmin.TB_STU_HEIGHTWEIGHT f
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