为了建立一个注册系统,我建立了一个数据库,存储学生和教授的课程。但是,我试图创建一个存储过程,检查classSize
属性是否大于或等于'Sections'表中的classSizeCap
属性。然而,我遇到了一个问题。PostgreSQL无法识别我的CASE
或IF
语句。我该怎么解决这个问题?
-- WILL SHOW WHAT STUDENTS NEED OVERRIDES FOR WHICH CLASS -- CREATE OR REPLACE FUNCTION startOverrides(REFCURSOR) RETURNS refcursor AS $$ DECLARE class_size INT := (PERFORM Sections.classSize FROM Sections INNER JOIN Enrollment ON Enrollment.courseID = Sections.courseID WHERE Enrollment.sectionNumber = Sections.sectionNumber); class_members INT := (PERFORM students_enrolled.courseName, COUNT(*) FROM students_enrolled GROUP BY students_enrolled.courseName, COUNT(*)); overrides REFCURSOR := $1; BEGIN OPEN overrides FOR CASE WHEN class_size >= class_members THEN SELECT students_enrolled.fname, students_enrolled.lname, students_enrolled.courseName, SUM(Sections.classSizeCap - Sections.classSize) AS overrides FROM students_enrolled INNER JOIN Sections ON Sections.courseID = students_enrolled.courseID AND Sections.sectionNumber = students_enrolled.sectionNumber GROUP BY students_enrolled.fname, students_enrolled.lname, students_enrolled.courseName, SUM(Sections.classSizeCap - Sections.classSize) END IF; RETURN overrids; END $$ LANGUAGE plpgsql; SELECT students_enrolled('overrides'); FETCH ALL FROM overrides;
我的错误:
ERROR: syntax error at or near "CASE" LINE 8: CASE WHEN (SELECT Sections.classSize ^ ********** Error ********** ERROR: syntax error at or near "CASE" SQL state: 42601 Character: 151
这是我主要使用的观点:
CREATE OR REPLACE VIEW students_enrolled AS SELECT People.fname, People.lname, Courses.courseID, Courses.courseName, Students.creditsEarned FROM Enrollment INNER JOIN People ON Enrollment.studentID = People.pid INNER JOIN Students ON Enrollment.studentID = Students.studentID INNER JOIN Courses ON Enrollment.courseID = Courses.courseID GROUP BY People.fname, People.lname, Courses.courseID, Courses.courseName, Students.creditsEarned;
这是我的数据库的快照:
-- 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, classSizecap 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) );
有什么建议吗?
因此,与其比较类大小属性,我意识到最好只做一个查询,说明谁注册了哪个类,按学分顺序显示某个类的这些人,然后从class SizeCap
值之后开始做一个限制。
这可以在限额
之后完成,您可以指定限额金额,然后使用偏移量
指示您要从哪里开始。然后,您可以使用所需的类和节调用函数。
-- WILL SHOW WHAT STUDENTS NEED OVERRIDES FOR WHICH CLASS -- CREATE OR REPLACE FUNCTION startOverrides(INT, INT, REFCURSOR) RETURNS refcursor AS $$ DECLARE class INT := $1; section INT := $2; class_size INT := (SELECT classSizeCap FROM Sections WHERE courseID = class AND sectionNumber = section); overrides REFCURSOR := $3; BEGIN OPEN overrides FOR SELECT * FROM students_enrolled WHERE (courseID = class AND sectionNumber = section) ORDER BY creditsEarned DESC LIMIT 1844674 OFFSET class_size; RETURN overrides; END $$ LANGUAGE plpgsql; SELECT startOverrides(222, 112, 'overrides'); FETCH ALL FROM overrides;
语法:
OPEN unbound_cursor FOR query;
CASE...
不是查询。请从SELECT
开始查询,并将谓词放入SELECT
的WHERE
子句中。
您可以使用INTO将结果分配给变量。
这样,您就可以将这两条select语句分配给一个变量,并在CASE
语句中比较它们。
-- WILL SHOW WHAT STUDENTS NEED OVERRIDES FOR WHICH CLASS --
CREATE OR REPLACE FUNCTION startOverrides(REFCURSOR) RETURNS refcursor AS
$$
DECLARE
class_size INT := 0;
class_members INT := 0;
overrides REFCURSOR := $1;
BEGIN
SELECT students_enrolled.courseName, COUNT(*)
FROM students_enrolled
GROUP BY students_enrolled.courseName, COUNT(*) INTO class_members;
SELECT Sections.classSize
FROM Sections
INNER JOIN Enrollment ON Enrollment.courseID = Sections.courseID
WHERE Enrollment.sectionNumber = Sections.sectionNumber
INTO class_members;
IF class_size >= class_members THEN
OPEN overrides FOR
SELECT students_enrolled.fname, students_enrolled.lname, students_enrolled.courseName, SUM(Sections.classSizeCap - Sections.classSize) AS overrides
FROM students_enrolled
INNER JOIN Sections ON Sections.courseID = students_enrolled.courseID
AND Sections.sectionNumber = students_enrolled.sectionNumber
GROUP BY students_enrolled.fname, students_enrolled.lname, students_enrolled.courseName, SUM(Sections.classSizeCap - Sections.classSize)
;
END IF;
RETURN overrids;
END
$$
LANGUAGE plpgsql;
我需要一个可以在junit 方法中调用的方法,该方法比较两个布尔值以检查它们是否相等,并返回一个布尔值。例如,类似这样的事情: 如果不相等,则返回false,如果相等,则返回true。我已经检查了布尔类,但是唯一接近的是< code>Boolean.compare(),它返回一个int值,我不能使用这个值。
问题内容: 我的存储过程中的这段代码: 是类型。 执行此存储过程时,我提供了 表明 //如果是正确的 代替 它在sql中的某个函数中,用于验证字符串是否为null或为空, 或者 我在某个地方犯了错误? 就像在C#中-> 问题答案: 这是正确的行为。 如果将@设置为一个值,则下面的表达式为true 无论如何,在SQL Server中都没有这样的功能,但是您可以创建自己的功能:
问题内容: 我正在写一个基本查询,类似: 我想然后通过使用类似的结果执行: 我的问题是,如何检查? 问题答案: 如果您希望查询可能经常返回行(尤其是很多行),也可以这样做,这可能会提供一个更好的短路机会: …因为它将在到达匹配的第一行后立即返回。 我不建议仅使用它,因为您每次都必须实现(忽略)整个结果集。
问题内容: 我想检查两个数组是否相等。我的意思是:相同的大小,相同的索引,相同的值。我怎样才能做到这一点? 根据用户的建议,如果数组中的至少一个元素不同,我希望以下内容可以打印 enter ,但实际上没有。 问题答案: $arraysAreEqual = ($a == $b); // TRUE if $a and $b have the same key/value pairs. $arraysA
问题内容: 我想知道如何检查两个功能是否相同。一个示例将评估为true。据我所知,Python将检查函数是否在内存中占据相同的位置,而不是它们是否具有相同的操作。我知道拥有该功能似乎不切实际。 另一个解决方案是我可以在函数上运行以查看其包含的内容或工作方式的某些方法。因此,其中一种将返回该方法的工作方式,可能是在字典中还是在某种形式中。 我希望得到一个答案,但我怀疑这是可能的。 问题答案: 如果您
正如您所看到的,我已经有了一个iscollding方法,控制台中的输出似乎是正确的,但是它在show()方法中不起作用,圆圈不会停止相互交叉。 那么我怎样才能使它起作用,当它碰撞时,位置被重新计算呢?