62,046
社区成员
发帖
与我相关
我的任务
分享
select StuID as 学号, (select TrueName from DB_Users where DB_Score.StuID=DB_Users.StuID) as 姓名 ,
sum(case ClassID WHEN '25' THEN Score END )AS 语文,
sum(case ClassID WHEN '26' THEN Score END )AS 数学,
sum(Score) as 总分,AVG(Score) as 平均分,
Rank() over ( order by sum(Score) ) as 名次
from DB_Score Where TypeID=1 and ImportTime='2014-1-14'
group by DB_Score.StuID order by 总分
var q = from a in DB_Score
group a by a.StuID into k
select new {
stuNum = k.Key,
lang = k.Where(b=>b.ClassID==1).Sum(b=>b.Score),
math = k.Where(b=>b.ClassID==2).Sum(b=>b.Score),
};
var p = q.Select(a=>new {
stuNum = a.stuNum,
lang = a.lang,
math = a.math,
total = (a.lang+a.math)
});
foreach(var item in p.OrderByDescending(a=>a.total)){}
public ActionResult AvgScore(int? TypeID, DateTime? AddTime, int? GradeID, int? ClassID)
{
string tempStr="";
var q = from a in context.DB_Score
join o in context.DB_Users on a.StuID equals o.StuID
where (a.TypeID == TypeID || string.IsNullOrEmpty(Convert.ToString(TypeID)))
&& (SqlMethods.DateDiffDay(a.AddTime, Convert.ToDateTime(AddTime)) == 0 || string.IsNullOrEmpty(Convert.ToString(AddTime)))
&& (o.GradeID == GradeID || string.IsNullOrEmpty(Convert.ToString(GradeID)))
&& (o.ClassID == ClassID || string.IsNullOrEmpty(Convert.ToString(ClassID)))
group a by a.StuID into k
select new
{
学号 = k.Key,
语文 = k.Where(b => b.ClassID == 25).Sum(b => b.Score),
数学 = k.Where(b => b.ClassID == 26).Sum(b => b.Score),
英语 = k.Where(b => b.ClassID == 35).Sum(b => b.Score),
政治 = k.Where(b => b.ClassID == 36).Sum(b => b.Score),
历史 = k.Where(b => b.ClassID == 37).Sum(b => b.Score),
生物 = k.Where(b => b.ClassID == 38).Sum(b => b.Score),
地理 = k.Where(b => b.ClassID == 39).Sum(b => b.Score),
物理 = k.Where(b => b.ClassID == 40).Sum(b => b.Score),
化学 = k.Where(b => b.ClassID == 41).Sum(b => b.Score),
综合文 = k.Where(b => b.ClassID == 55).Sum(b => b.Score),
综合理 = k.Where(b => b.ClassID == 56).Sum(b => b.Score),
平均分 = k.Average(b => b.Score),
总分 = k.Sum(b => b.Score)
};
var p = q.Select(a => new
{
学号 = a.学号,
语文 = a.语文,
数学 = a.数学,
英语 = a.英语,
政治 = a.政治,
历史 = a.历史,
生物 = a.生物,
地理 = a.地理,
物理 = a.物理,
化学 = a.化学,
综合文 = a.综合文,
综合理 = a.综合理,
平均分 = a.平均分,
总分 = a.总分
});
JsonClass jsonData = new JsonClass();
jsonData.Name("myList");
JsonClass jsonResult = new JsonClass();
jsonResult.Name("isNull");
int i = 1;
foreach (var item in p.OrderByDescending(a => a.总分))
{
jsonData.Add("mingci", i);
jsonData.Add("xuehao", item.学号);
jsonData.Add("xingming", Function.GetUserTrueName(item.学号));
jsonData.Add("yuwen", CheckHasVal(item.语文));
jsonData.Add("shuxue", CheckHasVal(item.数学));
jsonData.Add("yingyu", CheckHasVal(item.英语));
jsonData.Add("zhengzhi",CheckHasVal(item.政治));
jsonData.Add("lishi", CheckHasVal(item.历史));
jsonData.Add("shengwu", CheckHasVal(item.生物));
jsonData.Add("dili", CheckHasVal(item.地理));
jsonData.Add("wuli", CheckHasVal(item.物理));
jsonData.Add("huaxue", CheckHasVal(item.化学));
jsonData.Add("zonghewen",CheckHasVal(item.综合文));
jsonData.Add("zongheli", CheckHasVal(item.综合理));
jsonData.Add("pingjunfen", item.平均分);
jsonData.Add("zongfen", item.总分);
jsonData.BuildSection();
i++;
}
tempStr += jsonData.BuildJson(2, false);
jsonResult.Add(false);
tempStr += jsonResult.BuildJson(1, true);
ViewData["Result"] = "{" + tempStr + "}";
return View("Result");
}
最终完成了,谢谢大神提供的思路!