postgresql中的存储过程返回重复的列

Question

我正在尝试创建一个存储过程，根据学生所上的课程（显然）来创建学生时间表。但是，我认为连接导致存储过程返回双倍的行。

    CREATE OR REPLACE FUNCTION generateStudentSchedule(TEXT, REFCURSOR) RETURNS refcursor AS
    $$
    DECLARE
      tfname TEXT       := $1;
      ref    REFCURSOR  := $2;
    BEGIN
      OPEN ref FOR
        SELECT c.courseName, l.day, l.startTime, l.endTime 
        FROM ClassEvent l 
        JOIN Sections s ON l.courseID = s.courseID
        JOIN Courses c ON l.courseID = c.courseID
        JOIN Enrollment e ON e.courseID = l.courseID
        WHERE (e.studentID IN (SELECT studentID 
                  FROM Students 
                  WHERE studentID IN (SELECT pid 
                              FROM People 
                              WHERE fname = tfname))
        AND l.sectionNumber = e.sectionNumber)
        ORDER BY 
        CASE
          WHEN l.day = 'Monday' THEN 1
          WHEN l.day = 'Tuesday' THEN 2
          WHEN l.day = 'Wednesday' THEN 3
          WHEN l.day = 'Thursday' THEN 4
          WHEN l.day = 'Friday' THEN 5
        END ASC, l.starttime;
      RETURN ref;
    END
    $$ 
    LANGUAGE plpgsql; 

这是输出：输出

对如何解决这个问题有什么建议吗？

这是我的数据库表的快照

    -- HOLDS A SPECIFIC COURSE WITHOUT THE INSTANCES OF THE CLASS --
    CREATE TABLE Courses (
        courseID      SERIAL      UNIQUE NOT NULL,
        department    TEXT               NOT NULL,
        courseNumber  VARCHAR(10)        NOT NULL,
        courseName    TEXT        UNIQUE NOT NULL,
        credits       INT                NOT NULL,
        PRIMARY KEY(courseID)
    );

    -- HOLDS A SPECIFIC COURSE OFFERINGS --
    CREATE TABLE ClassesAvailable (
        courseID INT  NOT NULL,
        year     INT  NOT NULL,
        term     TEXT NOT NULL,
        CHECK(term = 'Fall' OR term = 'Winter' OR term = 'Spring' OR term = 'Summer'),
        PRIMARY KEY(courseID, year, term)
    );

    -- PEOPLE SUPERTYPE --
    CREATE TABLE People (
        pid   SERIAL            UNIQUE NOT NULL,
        fname TEXT                     NOT NULL,
        lname TEXT                     NOT NULL,
        PRIMARY KEY(pid)
    );

    -- HOLDS THE DIFFERENT PROFESSORS TEACHING AT THE SCHOOL --
    -- SUBTYPE OF PEOPLE --
    CREATE TABLE Professors (
        professorID  INT  UNIQUE NOT NULL,
        status       TEXT        NOT NULL,
        CHECK(status = 'Full-Time' OR status = 'Part-time'),
        PRIMARY KEY(professorID),
        FOREIGN KEY(professorID) REFERENCES People(pid)
    );

    -- HOLDS THE SPECIFIC INSTANCES OF THE CLASS DEPENDING ON THE YEAR AND TERM --
    CREATE TABLE Sections (
        courseID      INT          NOT NULL,
        year          INT          NOT NULL,
        term          TEXT         NOT NULL, 
        sectionNumber INT          NOT NULL,
        startDate     DATE         NOT NULL,
        endDate       DATE         NOT NULL,
        crn           INT          NOT NULL,
        classSize     INT          NOT NULL,
        CHECK(term = 'Fall' OR term = 'Winter' OR term = 'Spring' OR term = 'Summer'),
        PRIMARY KEY(courseID, year, term, sectionNumber),
        FOREIGN KEY(courseID, year, term) REFERENCES ClassesAvailable(courseID, year, term)
    );

    -- HOLDS THE EVENT OF THE CLASS --
    -- A CLASS MAY HAVE DIFFERENT DAYS ON WHICH --
    -- THEY MEET ON, SO THIS ALLOWS A CERTAIN --
    -- SECTION TO HAVE SEVERAL DAYS WITHOUT CONFLICT --
    CREATE TABLE ClassEvent (
        professorID   INT          NOT NULL,
        courseID      INT          NOT NULL,
        year          INT          NOT NULL,
        term          TEXT         NOT NULL,
        sectionNumber INT          NOT NULL,
        day           TEXT, 
        startTime     TIME,
        endTime       TIME,
        location      TEXT,
        campus        TEXT,
        CHECK(day = 'Monday' OR day = 'Tuesday' OR day = 'Wednesday' OR day = 'Thursday' OR day = 'Friday' OR day = 'Saturday' OR day = 'Sunday' OR day IS NULL),
        CHECK(term = 'Fall' OR term = 'Winter' OR term = 'Spring' OR term = 'Summer'),
        CHECK(campus = 'Main' OR campus = 'Online' OR campus = 'Italy'),
        PRIMARY KEY(professorID, courseID, year, term, sectionNumber, day, startTime, endTime),
        FOREIGN KEY(professorID) REFERENCES Professors(professorID),
        FOREIGN KEY(courseID, year, term, sectionNumber) REFERENCES Sections(courseID, year, term, sectionNumber)
    );

    -- GENERATES THE PREREQUESITES --
    CREATE TABLE Prerequisites (
        courseID      INT        NOT NULL,
        year          INT        NOT NULL,
        term          TEXT       NOT NULL,
        prereqID      INT        NOT NULL,
        CHECK(term = 'Fall' OR term = 'Winter' OR term = 'Spring' OR term = 'Summer'),
        PRIMARY KEY(courseID, year, term, prereqID),
        FOREIGN KEY(courseID, year, term) REFERENCES ClassesAvailable(courseID, year, term),
        FOREIGN KEY(prereqID) REFERENCES Courses(courseID)
    );


    -- HOLDS THE STUDENTS THAT WILL BE TAKING THE CLASSES --
    -- SUBTYPE OF PEOPLE --
    CREATE TABLE Students (
        studentID     INT  UNIQUE NOT NULL,
        gradYear      INT         NOT NULL,
        creditsEarned INT         NOT NULL,
        PRIMARY KEY(studentID),
        FOREIGN KEY(studentID) REFERENCES People(pid)
    );

    -- HOLDS A CLASS RECORD FOR STUDENTS (AND POSSIBLY PROFESSORS) --
    CREATE TABLE Enrollment (
        studentID     INT         NOT NULL,
        courseID      INT         NOT NULL,
        year          INT         NOT NULL,
        term          TEXT        NOT NULL,
        sectionNumber INT         NOT NULL,
        CHECK(term = 'Fall' OR term = 'Winter' OR term = 'Spring' OR term = 'Summer'),
        PRIMARY KEY(studentID, courseID, year, term, sectionNumber),
        FOREIGN KEY(studentID) REFERENCES Students(studentID),
        FOREIGN KEY(courseID, year, term, sectionNumber) REFERENCES Sections(courseID, year, term, sectionNumber)
    );

    -- HOLDS THE DIFFERENT DEGREES THAT CAN BE ATTAINED AT THE COLLEGE/UNIVERSITY --
    CREATE TABLE Degrees (
        degreeID     SERIAL       UNIQUE NOT NULL,
        degreeName   TEXT                NOT NULL,
        degreeType   TEXT                NOT NULL,
        degDepartment VARCHAR(4)         NOT NULL,
        CHECK(degreeType = 'Major' OR degreeType = 'Minor' OR degreeType = 'Masters'),
        PRIMARY KEY(degreeID)
    );

    -- HOLDS THE CLASSES THAT WILL MAKE UP A DEGREE --
    CREATE TABLE DegreeReq (
        degreeID INT REFERENCES Degrees(degreeID) NOT NULL,
        courseID INT REFERENCES Courses(courseID) NOT NULL,
        PRIMARY KEY(degreeID, courseID)
    );

    -- HOLDS THE INSTANCE OF A DEGREE FOR A CERTAIN STUDENT --
    -- FOR EXAMPLE: A STUDENT CAN HAVE A MAJOR AND A MINOR --
    -- SO HE/SHE CAN STORE THEM SEPARATELY --
    CREATE TABLE DegreeInstance (
        degreeID        INT  REFERENCES Degrees(degreeID)   UNIQUE NOT NULL,
        studentID       INT  REFERENCES Students(studentID) UNIQUE NOT NULL,
        startDate       DATE                                       NOT NULL,
        endDate         DATE                                       NOT NULL,
        creditsRequired INT                                        NOT NULL, 
        PRIMARY KEY(degreeID, studentID)
    );

    -- HOLDS ALL THE RATE MY PROFESSOR STATS --
    CREATE TABLE Rating (
        professorID      INT        UNIQUE NOT NULL,
        rmpID            BIGINT     UNIQUE NOT NULL,
        avgRating        FLOAT             NOT NULL,
        avgHelpfulness   FLOAT             NOT NULL,
        avgClarity       FLOAT             NOT NULL,
        avgEasiness      FLOAT             NOT NULL,
        PRIMARY KEY(professorID, rmpID),
        FOREIGN KEY(professorID) REFERENCES Professors(professorID)
    );

    -- HOLDS CLASS RECORDS FOR STUDENTS --
    CREATE TABLE ClassRecord (
        studentID  INT  NOT NULL,
        courseID   INT  NOT NULL,
        year       INT  NOT NULL,
        term       TEXT NOT NULL,
        grade      TEXT NOT NULL,
        CHECK(grade = 'A' OR grade = 'A-' OR grade = 'B+' OR grade = 'B' OR grade = 'B-' OR grade = 'C+' OR grade = 'C' OR grade = 'C-' OR grade = 'D+' OR grade = 'D' OR grade = 'D-' OR grade = 'F' OR grade = 'P'),
        PRIMARY KEY(studentID, courseID, year, term, grade),
        FOREIGN KEY(courseID, year, term) REFERENCES ClassesAvailable(courseID, year, term),
        FOREIGN KEY(studentID) REFERENCES Students(studentID)
    );

Answer 1

我知道有两个选项可以帮助你。您可以使用DISTINCT或groupby。

DISTINCT看起来像：

SELECT DISTINCT c.courseName, l.day, l.startTime, l.endTime

这确保只返回唯一的行。

groupby选项如下所示：

AND l.sectionNumber = e.sectionNumber)
GROUP BY c.courseName, l.day, l.startTime, l.endTime 
ORDER BY 

这也将确保只返回每个相似组的一行。

使用DISTINCT，您可以轻松地添加更多的选择列，而无需进行其他更改。DISTINCT将确保整行列是不同的。

对于GROUP BY，每次更改选择列时，还需要更新GROUP BY子句。

从性能的角度来看，当处理多个列时，GROUP BY往往是首选的。当获取单个列的所有不同值时，DISTINCT似乎更常见。