Oracle技术问询：从患者与预约表查询最大/最小记录

Got it, let's break this down. First, I see your Appointment table definition got cut off (the appDa... part), so I'll assume that's a appDate DATE column since that's standard for appointment records.

When you say "max and min records," you're probably referring to records with the highest or lowest values in a specific column (like patient age, appointment date, etc.). I'll cover the most common scenarios below for both tables:

Since we have the full schema here, let's start with this one.

Records for Oldest/Youngest Patients #

To get all patients who are the oldest (max age):

SELECT *
FROM Patient
WHERE p_age = (SELECT MAX(p_age) FROM Patient);

For the youngest patients (min age):

SELECT *
FROM Patient
WHERE p_age = (SELECT MIN(p_age) FROM Patient);

If you want both oldest and youngest patients in a single query, use IN:

SELECT *
FROM Patient
WHERE p_age IN (SELECT MAX(p_age) FROM Patient, SELECT MIN(p_age) FROM Patient);

Records for Most/Earliest Added Patients (assuming auto-incrementing patientID) #

If patientID is assigned sequentially, the highest ID would be the most recent patient:

SELECT *
FROM Patient
WHERE patientID = (SELECT MAX(patientID) FROM Patient);

And the lowest ID would be the first added patient:

SELECT *
FROM Patient
WHERE patientID = (SELECT MIN(patientID) FROM Patient);

Since your schema is truncated, I'll work with the standard appDate DATE column (swap this with your actual column name if it's different, like appTime or something else).

Records for Latest/Earliest Appointments #

Get all the most recent appointments:

SELECT *
FROM Appointment
WHERE appDate = (SELECT MAX(appDate) FROM Appointment);

Get all the earliest appointments:

SELECT *
FROM Appointment
WHERE appDate = (SELECT MIN(appDate) FROM Appointment);

Join with Patient Table to Get Full Patient Details #

If you want to see patient info alongside their max/min appointments, use a join:

-- Patient details + latest appointments
SELECT p.*, a.*
FROM Patient p
INNER JOIN Appointment a ON p.patientID = a.patientId
WHERE a.appDate = (SELECT MAX(appDate) FROM Appointment);

• If your Appointment table has other numeric fields (like staffId), just replace appDate with that field in the subqueries to get max/min based on that value.
• These queries return all matching records if there are duplicates (e.g., two patients with the same maximum age). If you only want one record even when duplicates exist (Oracle 12c+), use FETCH FIRST 1 ROW ONLY:

SELECT *
FROM Patient
ORDER BY p_age DESC
FETCH FIRST 1 ROW ONLY;

内容的提问来源于stack exchange，提问作者u_u-de