如何在SQL中获取每个元素的最大值

发布于 2024-12-11 07:07:15 字数 626 浏览 0 评论 0原文

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。有人对此有什么想法吗？

非常感谢

原文

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)
);

HEllo, I have these tables and I want to find for each PID the last Date he attended an appointment. I tried with joins but nothing worked. I am using Postgres. Does anyone have an idea about it?

Thank you very much

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

顾忌 2024-12-18 07:07:15

尝试一下，通过出席和预约加入。

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

Give this a try, joining through Attend and 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

For patients attending 2+ appointments per month you need COUNT() inside a HAVING clause.

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

回复收藏 0 原文

~没有更多了~