C#--C/S--学员管理系统--8--考试成绩查询（汇总查询）

以下是学习笔记：

效果：

1，统计全校考试信息

 

 2，班级考试信息

 

实现：

 1，后台代码部分

DAL--ScoreListService中添加

namespace DAL
{
    public class ScoreListService
    {
        #region 按照班级成绩查询列表

        /// <summary>
        /// 根据班级查询考试成绩列表
        /// </summary>
        /// <param name="className">班级名称</param>
        /// <returns></returns>
        public List<StudentExt> GetScoreList(string className)
        {
            string sql = "select Students.StudentId,StudentName,ClassName,CSharp,SQLServerDB from Students ";
            sql += "inner join StudentClass on StudentClass.ClassId=Students.ClassId ";//内连接 条件：StudentClass.ClassId=Students.ClassId
            sql += " inner join ScoreList on ScoreList.StudentId=Students.StudentId";
            if (className != null && className.Length != 0)
            {
                sql += string.Format(" where ClassName='{0}'", className);
            }
            SqlDataReader objReader = SQLHelper.GetReader(sql);
            List<StudentExt> list = new List<StudentExt>();//StudentExt扩展实体类
            while (objReader.Read())
            {
                list.Add(new StudentExt()//对象初始化器
                {
                    StudentId = Convert.ToInt32(objReader["StudentId"]),
                    StudentName = objReader["StudentName"].ToString(),
                    ClassName = objReader["ClassName"].ToString(),
                    CSharp = Convert.ToInt32(objReader["CSharp"]),
                    SQLServerDB = Convert.ToInt32(objReader["SQLServerDB"])
                });
            }
            objReader.Close();
            return list;
        }

        #endregion

        #region 全校考试成绩统计

        /// <summary>
        /// 获取全部考试的统计信息
        /// </summary>
        /// <returns></returns>
        public Dictionary<string, string> GetScoreInfo()
        {
            string sql = "select stuCount=count(*),avgCSharp=avg(CSharp),avgDB=avg(SQLServerDB) from ScoreList;";// 总数：stuCount=count(*)，平均：avgCSharp=avg(CSharp)
            sql += "select absentCount=count(*) from Students where StudentId not in(select StudentId from ScoreList)";//学号不在成绩表里面
            Dictionary<string, string> scoreInfo = null;
            SqlDataReader objReader = SQLHelper.GetReader(sql);
            if (objReader.Read())
            {
                scoreInfo = new Dictionary<string, string>();
                scoreInfo.Add("stuCount", objReader["stuCount"].ToString());
                scoreInfo.Add("avgCSharp", objReader["avgCSharp"].ToString());
                scoreInfo.Add("avgDB", objReader["avgDB"].ToString());
            }
            if (objReader.NextResult())//另外的一个结果集，上面有2个sql语句
            {
                if (objReader.Read())
                {
                    scoreInfo.Add("absentCount", objReader["absentCount"].ToString());
                }
            }
            objReader.Close();
            return scoreInfo;
        }

        /// <summary>
        /// 获取所有未参考考试的学员名单
        /// </summary>
        /// <returns></returns>
        public List<string> GetAbsentList()
        {
            string sql = "select StudentName from Students where StudentId not in(select StudentId from ScoreList)";//学号不在成绩表里面
            SqlDataReader objReader = SQLHelper.GetReader(sql);
            List<string> list = new List<string>();
            while (objReader.Read())
            {
                list.Add(objReader["StudentName"].ToString());
            }
            objReader.Close();
            return list;
        }

        #endregion

        #region 按照班级统计考试信息

        public Dictionary<string, string> GetScoreInfoByClassId(string classId)
        {
            string sql = "select stuCount=count(*),avgCSharp=avg(CSharp),avgDB=avg(SQLServerDB) from ScoreList ";
            sql += "inner join Students on Students.StudentId=ScoreList.StudentId where ClassId={0};";
            sql += "select absentCount=count(*) from Students where StudentId not in";
            sql += "(select StudentId from ScoreList) and ClassId={1}";
            sql = string.Format(sql, classId, classId);
            SqlDataReader objReader = SQLHelper.GetReader(sql);
            Dictionary<string, string> scoreInfo = null;
            if (objReader.Read())//读取考试成绩统计结果
            {
                scoreInfo = new Dictionary<string, string>();
                scoreInfo.Add("stuCount", objReader["stucount"].ToString());
                scoreInfo.Add("avgCSharp", objReader["avgCSharp"].ToString());
                scoreInfo.Add("avgDB", objReader["avgDB"].ToString());
            }
            if (objReader.NextResult())//读取缺考人数列表
            {
                if (objReader.Read())
                {
                    scoreInfo.Add("absentCount", objReader["absentCount"].ToString());
                }
            }
            objReader.Close();
            return scoreInfo;
        }
        /// <summary>
        /// 查询未参加考试的学生名单
        /// </summary>
        /// <returns></returns>
        public List<string> GetAbsentListByClassId(string classId)
        {
            string sql = "select StudentName from Students where StudentId not in ";
            sql += "(select StudentId from ScoreList) and ClassId={0}";
            sql = string.Format(sql, classId);
            SqlDataReader objReader = SQLHelper.GetReader(sql);
            List<string> list = new List<string>();
            while (objReader.Read())
            {
                list.Add(objReader["StudentName"].ToString());
            }
            objReader.Close();
            return list;
        }

        #endregion

        #region  基于数据集DataSet的数据查询

        /// <summary>
        /// 获取所有的考试信息（存储在DataSet中）
        /// </summary>
        /// <returns></returns>
        public DataSet GetAllScoreList()
        {
            string sql = "select Students.StudentId,StudentName,ClassName,CSharp,SQLServerDB";
            sql += " from Students ";
            sql += " inner join StudentClass on StudentClass.ClassId=Students.ClassId";
            sql += " inner join ScoreList on ScoreList.StudentId=Students.StudentId";
            return SQLHelper.GetDataSet(sql);
        }

        #endregion

    }

　　2，UI部分：

统计全校成绩代码：

        //统计全校考试成绩
        private void btnStat_Click(object sender, EventArgs e)
        {
            this.gbStat.Text = "全校考试成绩统计";
            //查询成绩列表
            this.dgvScoreList.AutoGenerateColumns = false;//禁止生成不需要的数据
            this.dgvScoreList.DataSource = objScoreService.GetScoreList("");
            //查询并显示成绩统计
            Dictionary<string, string> dic = objScoreService.GetScoreInfo();
            this.lblAttendCount.Text = dic["stuCount"];
            this.lblDBAvg.Text = dic["avgDB"];
            this.lblCSharpAvg.Text = dic["avgCSharp"];
            this.lblCount.Text = dic["absentCount"];
            //显示缺考的人员姓名
            List<string> list = objScoreService.GetAbsentList();
            this.lblList.Items.Clear();//每次显示要把前面的清除掉
            this.lblList.Items.AddRange(list.ToArray());//AddRange 一次性添加
        }

　　统计班级成绩代码：

        //根据班级查询      
        private void cboClass_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (this.cboClass.SelectedIndex == -1)
            {
                MessageBox.Show("请首先选择要查询的班级", "查询提示");
                return;
            }
            this.dgvScoreList.AutoGenerateColumns = false;
            this.dgvScoreList.DataSource = objScoreService.GetScoreList(this.cboClass.Text.Trim());
            //同步显示班级考试信息
            this.gbStat.Text = "[" + this.cboClass.Text.Trim() + "]考试成绩统计";
            Dictionary<string, string> dic =
                objScoreService.GetScoreInfoByClassId(this.cboClass.SelectedValue.ToString());
            this.lblAttendCount.Text = dic["stuCount"];
            this.lblCSharpAvg.Text = dic["avgCSharp"];
            this.lblDBAvg.Text = dic["avgDB"];
            this.lblCount.Text = dic["absentCount"];
            //显示缺考人员姓名
            List<string> list =
                objScoreService.GetAbsentListByClassId(this.cboClass.SelectedValue.ToString());
            this.lblList.Items.Clear();
            if (list.Count == 0) this.lblList.Items.Add("没有缺考");
            else lblList.Items.AddRange(list.ToArray());
        }