Question

我费了很大劲才想打听一下:

选择所有未完成特定星期的同行审查的学生。

每周,每个学生都必须对同一群体的同龄人进行同侪审查。

每个群体的规模可能不同,这就是我所面临的问题。

这是我的当前测试数据:

“https://i.sstatic.net/2a93b.png' alt=“同行审查表”/>

“https://i.sstatic.net/KYQBP.png' alt=“学生表”/>

Table 1: peer review table
Table 2: student table.

以下是我最初的询问, 所有的学生都根据他们做的同行审查数量分组。我现在需要检查 count(*) 是否小于每个学生的组规模 :

SELECT * 
FROM peerreview
RIGHT JOIN student 
ON student. studentID = peerreview.reviewer
WHERE week = 11
GROUP BY studentID
HAVING Count(*) < ????

Answer 1

之后的查询将返回已审查过同一组中所有学生的学生。

 SELECT a.reviewer, 
       a.groupid 
FROM   (SELECT student2.studentID AS reviewer, 
               student1.groupid, 
               Count(*)           AS cnt 
        FROM   student student1 
               INNER JOIN peerreview 
                       ON student1.studentID = peerreview.reviewee 
               INNER JOIN STUDENT STUDENT2 
                       ON student2.studentID = peerreview.reviewer 
        WHERE  student2.groupid = student2.groupid 
               AND peerreview.week = 11 
        GROUP  BY student1.groupid, 
                  student2.studentID) a 
       INNER JOIN (SELECT groupid, 
                          Count(*) - 1 AS cnt 
                   FROM   student 
                   GROUP  BY groupid) b 
               ON a.groupid = b.groupid 
                  AND a.cnt = b.cnt

http://www.sqlfiddle.com/#!2/bab2e/9" rel="no follow"见SqlFiddle

Answer 2

Select S.StudentId As Reviewer
    , S1.StudentId As StudentYetToBeReviewed
    , Weeks.WeekNum
From Student As S
    Join Student As S1
        On S1.GroupId = S.GroupId
            And S1.StudentId <> S.StudentId
    Cross Join  (
                Select 7 As WeekNum
                Union All Select 11
                ) As Weeks
Where Not Exists    (
                    Select 1
                    From PeerReview As P1
                    Where P1.reviewee = S1.StudentId
                        And P1.Week = Weeks.WeekNum
                    )
Order By WeekNum, reviewer

这将为您提供一份按周排列的审查员和他们需要审查的人的名单。在真正的解决方案中,您希望用一份单独的审查周清单来取代“交叉加入 ” ( Crossjoin ) 。

< a href=" "http://www.sqlfiddle.com/#!2/b02c4/1, rel="nofollow" >SQL Fiddle 版本

Answer 3

  select distinct s1.* 
  from student s1 inner join student s2 on s1.groupId = s2.groupeId
               left join peerreview pr on pr.revieweer = s1.studentId
                    and pr.reviewee =  s2.studentId
  where pr.Week = ? and  pr.revieweer is null and s1.studentId <> s2.studentId

友情链接