求:下面的语句是可以运行的,单结果不对。现在我要不同学校不同班级前35人的(高分到低分,不足的按实际人数计算)的相应学科的平均分,要如何修改???语句:
string mmk = "select 区县,学校,年级,班级,count(*) as 实有人数,SUM(CASE WHEN " + DropDownList1.SelectedValue + ">=0 THEN 1 ELSE 0 END) as 实考人数,sum(" + DropDownList1.SelectedValue + ") as " + DropDownList1.SelectedValue + "总分,CAST(sum(" + DropDownList1.SelectedValue + ")/SUM(CASE WHEN " + DropDownList1.SelectedValue + ">=0 THEN 1 ELSE 0 END) AS DECIMAL(10,2)) as " + DropDownList1.SelectedValue + "平均分,max(" + DropDownList1.SelectedValue + ") as " + DropDownList1.SelectedValue + "最高分,min(distinct " + DropDownList1.SelectedValue + ") as " + DropDownList1.SelectedValue + "最低分,(max(" + DropDownList1.SelectedValue + "))-(min(distinct " + DropDownList1.SelectedValue + ")) as " + DropDownList1.SelectedValue + "全距 from (Select top 35 percent " + DropDownList1.SelectedValue + ",区县,学校,年级,班级,考试名称 from " + DropDownList2.SelectedValue + "成绩 Group by " + DropDownList1.SelectedValue + ",区县,学校,年级,班级,考试名称 Order by " + DropDownList1.SelectedValue + " ASC) AS T where 考试名称='" + Label4.Text + "' group by 区县,学校,年级,班级 order by 年级," + DropDownList1.SelectedValue + "平均分 DESC";
结果:
人数计算不正确,请问如何修改?谢谢!