sql - SQL中如何取每个元素的最大值

Question

CREATE TABLE Department(
    DNumber integer NOT NULL PRIMARY KEY);

CREATE TABLE Patient(
    PID integer NOT NULL PRIMARY KEY,
    P_DNumber integer NOT NULL REFERENCES Department (DNumber)
);

CREATE TABLE Appointment(
    AppNumber integer NOT NULL PRIMARY KEY,
    AppDate date NOT NULL,
    App_DNumber integer  NOT NULL  REFERENCES Department (DNumber)
);

CREATE TABLE Attend(
    A_PID integer NOT NULL REFERENCES Patient (PID),
    A_AppNumber integer NOT NULL REFERENCES Appointment(AppNumber)
);

你好，我有这些表格，我想为每个 PID 找到他参加约会的最后日期。我尝试加入但没有任何效果。我正在使用 Postgres。有人对此有想法吗？

非常感谢

Answer 1

尝试一下，通过Attend 和Appointment 加入。

SELECT 
  Patient.PID,
  MAX(Appointment.AppDate) AS lastAttended
FROM 
  Patient 
  LEFT JOIN Attend ON Patient.PID = Attend.A_PID
  JOIN Appointment ON Appointment.AppNumber = Attend.A_AppNumber
GROUP BY Patient.PID

对于每月参加 2 次以上预约的患者，您需要在 HAVING 子句中使用 COUNT()。

SELECT 
  Patient.PID,
  COUNT(Attend.A_AppNumber) AS numAppts
FROM 
  Patient 
  LEFT JOIN Attend ON Patient.PID = Attend.A_PID
GROUP BY Patient.PID
HAVING COUNT(Attend.A_AppNumber) >= 2