sql - 适当的SELECT陈述式

我有以下表格：

CREATE TABLE Subject
(
    Subject_Code      INTEGER,
    Subject_Year      VARCHAR (8),
    PRIMARY KEY (Subject_Code, Subject_Year),
    Teacher_ID INTEGER REFERENCES
);

CREATE TABLE Teacher
(
    TeacherID        INTEGER PRIMARY KEY,
    FirstName         TEXT,
    Department_ID INTEGER References Academic Department(Department_ID)
);

CREATE TABLE Subject-taken
(
    Marks_Obtained   INTEGER,
    Subject_Code      INTEGER REFERENCES subject (Subject_Code),
    Candidate_ID       INTEGER REFERENCES Candidate (Candidate_ID),
    PRIMARY KEY (Subject_Code, Candidate_ID)
);

CREATE TABLE Academic_Department
(
    Department_ID     INTEGER PRIMARY KEY,
    Department_Name   TEXT
);

我已经试过下面的select语句

  SELECT m.subject_code,
         MIN (marks_obtained) AS Min_Marks,
         MAX (marks_obtained) AS Max_Marks
    FROM Subject-taken m, Subject a
GROUP BY m.Subject_Code;

想使用join函数任何关于在何处使用它的建议，以便与学科和学生一起加入系

最佳答案

使用联接在表之间链接数据。使用GROUPBY按某些字段进行统计。你可以试试这样的方法：

SELECT
    Subjects.Subject_Code,
    Subjects.Subject_Name,
    Teachers.TeacherID,
    Academic_Department.Department_ID,
    min(Subject-taken.Marks_Obtained) as min_marks,
    max(Subject-taken.Marks_Obtained) as max_marks,
    avg(Subject-taken.Marks_Obtained) as avg_marks,
    stddev_samp(subject-taken.Marks_Obtained) as stddev_marks
FROM
    Subjects LEFT JOIN
    Teachers ON Subjects.TeacherID = Subjects.TeacherID LEFT JOIN
    Academic_Department ON Teachers.Department_ID = Academic_Department.Department_ID LEFT JOIN
    Subject-taken ON Subjects.Subject_Code = Subject-taken.Subject_Code
GROUP BY
    Subjects.Subject_Code,
    Subject.Subject_Name,
    Teacher.TeacherID,
    Academic_Department.Department_ID

我不知道stddev_samp是否是您需要的聚合函数，stddev_pop也可用。请参阅this PostgreSQL documentation table了解详情。

关于sql - 适当的SELECT陈述式，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/55535602/

的Select

sql - 适当的SELECT陈述式