SQL 各班男女的BMI平均數

本文由 cwhk 在 2020-07-13 發表於 "WebSAMS 討論區" 討論區

標籤:
  1. 55338388

    cwhk
    Expand Collapse

    文章:
    24
    讚:
    0
    討論區已有選取BMI的SQL,但ESDA要求各班男女的BMI平均數,請問如何修改SQL?謝謝。
     
  2. 58521906

    edb-catherinewschan
    Expand Collapse

    文章:
    124
    讚:
    0
    請問BMI 在WEBSAMS那裡輸入呢??
     
    #2 edb-catherinewschan, 2020-07-14
  3. 55338388

    cwhk
    Expand Collapse

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

    edb-catherinewschan
    Expand Collapse

    文章:
    124
    讚:
    0
    請給我SQL 看看
     
    #4 edb-catherinewschan, 2020-07-15
  5. 55338388

    cwhk
    Expand Collapse

    文章:
    24
    讚:
    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'
    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
     
  6. 58521906

    edb-catherinewschan
    Expand Collapse

    文章:
    124
    讚:
    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'
    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
     
    #6 edb-catherinewschan, 2020-07-17
  7. 55338388

    cwhk
    Expand Collapse

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

    edb-catherinewschan
    Expand Collapse

    文章:
    124
    讚:
    0
    你好,

    用以下的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的規格放上去試行。

    upload_2020-7-22_14-6-42.png



    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
     

    附件文件:

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