代码之家 › 专栏 › 技术社区 › Kamyar

基于多对多关系过滤结果

many-to-many sql-server .net

Kamyar · 技术社区 · 14 年前

考虑以下场景:(SQL Server或MS Access)
学生
--学生ID

表格:
--StudentID(外键)

--学生成绩(满分100分)

现在我想过滤结果,只返回所有成绩都超过50分的学生的成绩详细信息。
获取所有学生和成绩:

DataTable dtAll = GetData("SELECT * FROM Students INNER JOIN Grades ON Students.StudentID = Grades.StudentID)

2- 找所有至少有一个年级的学生<50。(不及格的学生)

    DataTable dtFailed = GetData("SELECT Distinct(Students.StudentID), Grades.StudentGrade FROM Students INNER JOIN INNER JOIN Grades ON Students.StudentID = Grades.StudentID WHERE Grades.StudentGrade < 50")

3- 删除与失败学生相关的所有字段:

foreach (DataRow failedRow in dtFailed.Rows)
        {
            int counter = 0;
            while (counter < dtAll.Rows.Count)
            {
                if (counter >= dtAll.Rows.Count)
                    break;
                curRow = dtAll.Rows[counter];

                if (Convert.ToInt32(curRow["StudentID"]) == Convert.ToInt32(failedRow["StudentlID"]))
                    dtAll.Rows.Remove(curRow);
                else
                    counter += 1;
            }
        }

这段代码只是遍历所有dtFailed行,并删除dtAll中包含dtFailed项的行。(如果学生至少有一门课程不及格,所有学生的成绩将被取消。) 我想这是生产环境中常见的场景,希望有一种更快更干净的方法来实现这一点。如果有人和我分享,我会很感激的。
注意:如果这只能通过存储过程或任何特定于SQL的方式(而不是访问)来完成,请共享它,因为如果需要,我可以使用SQL Server而不是MS access。

2 回复 | 直到 14 年前

Martin Smith 14 年前

SELECT s.StudentId  ,
       s.StudentName,
       g.StudentGrade
FROM   Students s
       JOIN Grades g
       ON     g.StudentID = s.StudentID
WHERE  g.StudentGrade     > 50
AND    NOT EXISTS
       (SELECT *
       FROM    Grades g2
       WHERE   g2.StudentGrade <= 50
       AND     g.StudentID   = g2.StudentID)

p.campbell 14 年前

或许可以使用LINQ查询 dtAll 而不是再次访问数据库?

//student IDs having min grade greater than 50
 var studentIDs = from f in table.AsEnumerable()
                         group f by new { StuID = f.Field<int>("StudentID"), 
                                       StuGrade = f.Field<int>("StudentGrade") } 
                         into g
                         where g.Min(x => x.Field<int>("StudentGrade") > 50)
                         select new
                         {
                             StudentID = g.Key.StuID,
                             StudentGrade = g.Key.StuGrade
                         };


foreach (var item in studentIDs)
{
    Console.WriteLine(item.StudentID + " : " + item.StudentGrade);
}