循环遍历结果集以搜索不同表上的另一个值

Question

我想在结果表中搜索一些特定结果，使用不同表中的 id 根据从 studentInfo 表中选择的学生 ID 计算 cgpa，以下是我尝试过的，问题是它只是计算第一个 Id 或更确切地说是学生

    String lev = levelCombo.getSelectedItem().toString();
    int leve = Integer.parseInt(lev);
    int le = leve / 100;
    try {
        String sql
                = "select idNumber from studentInfo where level ='" + lev + "'  ";
        pst = conn.prepareStatement(sql);
        rs = pst.executeQuery();
        while (rs.next()) {
            String idNumb = rs.getString("idNumber");
            cgpa(idNumb, le);
            System.out.print("" + idNumb);
        }
    } catch (Exception e) {
    }
}
public void cgpa(String idnumber, int level) {
    float units = 0;
    float ugps = 0;
    float cgp = 0;
    for (int i = 1; i <= level; i++) {
        try {
            String sql = "select sum(UNIT) ,sum(UGP) ,round(sum(UGP) / NULLIF(sum(UNIT), 0), 2) from Results where ID_NUMBER =  ? and  level ='" + i + "'  ";
            pst = conn.prepareStatement(sql);
            pst.setString(1, idnumber);
            rs = pst.executeQuery();
            while (rs.next()) {
                Float un = rs.getFloat("sum(UNIT)");
                Float gp = rs.getFloat("sum(UGP)");
                //  Float sum=rs.getFloat("round(sum(UGP)/NULLIF(sum(UNIT), 0),2)");
                units += un;
                ugps += gp;
                cgp = ugps / units;
                String sql1 = "update academicstatus set cgpa ='" + cgp + "'  ,product='" + ugps 
                        + "',unit='" + units + "' where idnumber = ? ";
                updateAcademicstatus(cgp, ugps, units, idnumber);
                pst = conn.prepareStatement(sql1);
                pst.setString(1, idnumber);
                pst.executeUpdate();
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            try {
                rs.close();
                pst.close();
            } catch (Exception e) {
            }
        }
    }
}

Answer 1

您是否正在尝试单个查询

select  sum(UNIT),sum(UGP),round(sum(UGP)/ ULLIF(sum(UNIT),0),2)idNumber from studentInfo sinfo
join Results re on re.ID_NUMBER = sinfo.idNumber where level='"+lev+"'"