如何在SQL Join中消除重复项? - How can I eliminate duplicates in a SQL Join?

How can I eliminate duplicates in a SQL Join?

本文关键字：SQL Join | 更新日期: 2023-09-27

我正在为我的大学做一个基于web的php应用程序，我需要显示有多少学生同时参加一些课程，以便他们可以避免调度冲突。

This my schema:

studentID   course   grade   program
324532      csc232   0       computer science

我正在使用php，这是SQL查询。我给0分是因为他/她现在正在上课。

$result = mysql_query("SELECT s1.course AS c1 , s2.course AS c2 ,count(*) AS count 
FROM student s1 ,student s2  
WHERE  s1.studentID = s2.studentID 
AND s1.course!=s2.course AND s1.grade='0' AND s2.grade='0' GROUP BY s1.course,s2.course");

我得到的答案是这样的:

cscs321   csci322      6 // there is 6 students taking this two courses together
csci321   csci113      4 // there is 4 students taking these two subjects together

问题是我得到了一些重复的结果。例如csci321、csc322与csci322、csci321相同。

如何避免这种重复?

你可以试试这个:

SELECT
    s1.course AS c1,
    s2.course AS c2,
    count(s2.studentID) AS count
FROM student s1 
JOIN student s2 ON (
    s1.studentID = s2.studentID 
    AND s1.course<s2.course  -- <-- this line is important
    AND s1.grade='0' 
    AND s2.grade='0'
    )
GROUP BY
    s1.course,
    s2.course;

思路是每行对课程进行"排序"，因此

csci321, csc322

和

csc322, csci321

被认为是相同的，因为csc233 < csci321

您应该使用SUM而不是COUNT，因为您想要的是学生总数而不是计数。