linq 操作

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace dowqjdio
{
public class Student
{
public string SNo { get; set; }
public string Sex { get; set; }
public string SName { get; set; }
public int Age { get; set; }
}

public class Course
{
public string CNo { get; set; }
public string CName { get; set; }
}

public class Score
{
public string SNo { get; set; }
public string CNo { get; set; }
public decimal Degree { get; set; }
}
class Program
{
static void Main(string[] args)
{
List<Student> students = new List<Student>()
{
new Student() { Age=21, Sex="男", SNo="001", SName="学生1" },
new Student() { Age=22, Sex="女", SNo="002", SName="学生2" },
new Student() { Age=23, Sex="男", SNo="003", SName="学生3" },
new Student() { Age=24, Sex="女", SNo="004", SName="4学生" },
new Student() { Age=25, Sex="男", SNo="005", SName="学生5" },
new Student() { Age=26, Sex="女", SNo="006", SName="学生6" },
new Student() { Age=26, Sex="女", SNo="007", SName="学生7" },
};

List<Course> courses = new List<Course>()
{
new Course() { CName="语文" ,CNo="C0001"},
new Course() { CName="数学" ,CNo="C0002"},
new Course() { CName="英语" ,CNo="C0003"},
};

List<Score> score = new List<Score>() {
new Score() { CNo="C0001", SNo="001", Degree=50 },
new Score() { CNo="C0002", SNo="001", Degree=80 },
new Score() { CNo="C0003", SNo="001", Degree=70 },
new Score() { CNo="C0001", SNo="002", Degree=60 },
new Score() { CNo="C0002", SNo="002", Degree=40 },
new Score() { CNo="C0003", SNo="002", Degree=70 },
new Score() { CNo="C0002", SNo="003", Degree=70 },
new Score() { CNo="C0003", SNo="003", Degree=90 },
new Score() { CNo="C0003", SNo="004", Degree=57 },
new Score() { CNo="C0001", SNo="004", Degree=77 },
new Score() { CNo="C0003", SNo="005", Degree=57 },
new Score() { CNo="C0001", SNo="005", Degree=97 },
new Score() { CNo="C0003", SNo="006", Degree=67 },
new Score() { CNo="C0001", SNo="006", Degree=87 },
new Score() { CNo="C0003", SNo="006", Degree=67 },
new Score() { CNo="C0001", SNo="006", Degree=77 },
};
#region 练习
////按列查询
//sql: select* from table
//var result = from sc in students
// select sc;
//var result1 = from s in students
// select new
// {
// s.SName,
// s.SNo
// };

//foreach (var item in result1)
//{
// Console.WriteLine(item.SNo);
//}
////条件查询
////var result2 =( from s in students
//// where s.Age<24 && s.Sex=="男"
//// select new
//// {
//// s.SName,
//// s.SNo
//// }).ToList();
////var lamdaRes = students.Where(s => s.Age < 24 && s.Sex == "男")
//// .Select(s => new { s.SNo, s.SName });
////foreach (var item in lamdaRes)
////{
//// Console.WriteLine(item.SName);
////}
////distinct去重查询
//var result3 = (from s in students
// select new
// {
// s.Sex
// }).Distinct().ToList();

//var lamdaRes1 = students.Select(s => s.Sex).Distinct();

////foreach (var item in result3)
////{
//// Console.WriteLine(item.Sex);
////}
////两个区间内查询
////var result4 = from s in students
//// where s.Age>21&&s.Age<24
//// select s;
////foreach (var item in result4)
////{
//// Console.WriteLine(item.SName);
////}
////在一个范围内查询{2,3,3} sql sno in('001','003')
////var array = new string[] { "001", "003" };
////var result5 = from s in students
//// where array.Contains(s.SNo)
//// select s;
////foreach (var item in result5)
////{
//// Console.WriteLine(item.SName);
////}
////或关系查询
////排序
//var result6 = from s in students
// orderby s.Age descending
// select s;
////foreach (var item in result6)
////{
//// Console.WriteLine(item.SName);
////}
////行数查询

//var result7 = (from s in students
// orderby s.Age descending
// select s).Count();

//Console.WriteLine(result7);
////平均值查询
////var result8 = (from s in score
//// where s.CNo== "C0002"
//// select s.Degree).Average();

////Console.WriteLine(result8);
////分组
//var result9 = from s in score
// group s by s.CNo
// into sc
// select new
// {
// Count = sc.Count(),
// CNo = sc.Key
// };
//foreach (var item in result9)
//{
// Console.WriteLine(item.CNo + ":" + item.Count);
//}
////模糊查询 学生姓名 4
////var result10= (from s in students
//// where s.SName.StartsWith("4")
//// select s).ToList();
////var result10 = (from s in students
//// where s.SName.StartsWith("4")
//// select s).ToList();

////Console.WriteLine(result10[0].SName);
////多表联合查询
////学生的姓名 课程的名字 成绩

////var result11 = from s in score
//// join stu in students
//// on s.SNo equals stu.SNo
//// join cour in courses
//// on s.CNo equals cour.CNo

//// select new
//// {
//// stu.SName,
//// cour.CName,

//// s.Degree
//// };
//var lamdaRes3 = score.Join(students, s => s.SNo,
// stu => stu.No, (s, stu) => new
// {
// stu.SName,
// s.Degree
// }
// );
//foreach (var item in lamdaRes3)
//{
// var r = string.Format("{0}---{1}", item.SName, item.Degree);
// Console.WriteLine(r);
//}
////分页查询
//var result12 = (from s in students
// select s).Skip(3).Take(3);
////foreach (var item in result12)
////{
//// Console.WriteLine(item.SName);
////}
#endregion
//=====================================作业========================================
#region 作业
//作业 使用lamda表达式或者linq实现： 1.所有科目的及格人数 2.查询每个科目最高分的学生信息 3.查询平均成绩前五名的学生信息
//1.科目，及格人数
Console.WriteLine("------------------第一题------------------------");
var lamdaRes3 = from sco in score
join cour in courses
on sco.CNo equals cour.CNo
where (sco.Degree >= 60)
group sco by cour.CName
into re
select new
{
CName = re.Key,
Count = re.Count()
};
foreach (var item in lamdaRes3)
{
var r = string.Format("{0}---科目的及格人数{1}", item.CName, item.Count);
Console.WriteLine(r);
}
//2.科目，学生名字，成绩
Console.WriteLine("---------------------第二题-----------------------");
var resultQuery = (from sco in score
join cour in courses
on sco.CNo equals cour.CNo
group sco by cour.CName
into re
select new
{
cname = re.Key,
source = re.OrderByDescending(tt => tt.Degree).FirstOrDefault().Degree,
sname = (from stu in students
where stu.SNo == re.OrderByDescending(tt => tt.Degree).FirstOrDefault().SNo
select stu.SName).ToList()[0]
});
foreach (var item in resultQuery)
{
var r1 = string.Format("科目{0}---{1}---成绩{2}", item.cname, item.sname, item.source);
Console.WriteLine(r1);
}

//3.平均成绩，学生名字
Console.WriteLine("--------------------第三题---------------------");
var resultQuery2 = (from s in score
join stu in students
on s.SNo equals stu.SNo
join cour in courses
on s.CNo equals cour.CNo
group s by stu.SName into g
select new
{
snam = g.Key,
score = g.Average(c => c.Degree)
}).OrderByDescending(s => s.score).Take(5);
foreach (var item in resultQuery2)
{
var r1 = string.Format("---学生名字{0}--平均成绩{1}", item.snam, item.score);
Console.WriteLine(r1);
}
#endregion
Console.ReadKey();
}
}
}