sql - 列出较早入院但未进行任何手术的出院患者

这基本上是一个完整的答案。它表明更清楚地编写 SQL，并使用建议的JOIN / ON形式，可以更清楚地指出何时缺少连接条件：

更新以允许没有温度观察的患者

SELECT patient_id AS "Patient ID"
     , CONCAT(CONCAT(first_name,' '),surname) AS "Patient Name"
     , admission_date AS "Date Admitted"
     , discharge_date AS "Date Discharged"
     , ROUND(AVG(observ_value),2) AS "Average Temperature"
     , daily_charge AS "Expenses"
  FROM person
  JOIN admission
    ON person.person_id = admission.patient_id
   AND admission.admission_id NOT IN (SELECT operation.admission_id FROM operation)
   AND discharge_date IS NOT NULL
  JOIN ward
    ON ward.ward_code = admission.ward_code
  LEFT JOIN observation
    ON observ_type = 'Temp'
   AND observation.admission_id = admission.admission_id
 GROUP BY patient_id, admission_date, discharge_date, daily_charge, first_name, surname
 ORDER BY admission_date
;

请注意与表ON相关的子句中缺少的连接条件。observation仅指定observ_type是不够的。

添加了示例数据并具有更正连接条件的结果：

具有相同数据且缺少连接条件的旧结果：

这是部分旧查询结果（来自问题）以及来自 OP 的完整数据

这是包含完整数据的新结果，上面提出了解决方案：

这允许没有温度观察的患者。

注意：问题未涉及的逻辑中可能存在其他问题。原始 SQL 缺少连接标准肯定会造成混乱。现在已经清理干净了。

以下是问题应提供的详细信息，以及一些可用的测试数据，这些数据会产生您意想不到的结果：

alter session set NLS_DATE_FORMAT='DD/MM/YYYY';

CREATE TABLE PERSON
(
  Person_id      NUMBER(3) PRIMARY KEY,
  Surname        VARCHAR2(20),
  First_name     VARCHAR2(20),
  Sex            CHAR(1),
  Birth_date     DATE,
  Street         VARCHAR2(40),
  Town           CHAR(20),
  Postcode       NUMBER(4),
  Next_of_kin    NUMBER(3)
);

CREATE TABLE STAFF
(
  Person_id     NUMBER(3) PRIMARY KEY,
  Start_date    DATE,
  Staff_type    VARCHAR2(15),
  Charges       NUMBER(10,2),
  Resign_date   DATE,
  FOREIGN KEY (Person_id) references PERSON(Person_id)
);

CREATE TABLE WARD (
   Ward_code         CHAR(3) PRIMARY KEY,
   Ward_name         VARCHAR2(20),
   Bed_count         NUMBER(4),
   Opened_date       DATE,
   Last_painted_date DATE,
   Daily_charge      NUMBER(10,2)
);

CREATE TABLE OPERATION_TYPE (
   Op_code         CHAR(3) PRIMARY KEY,
   Operation_name  VARCHAR2(50),
   Theatre_fee     NUMBER(10,2),
   Days_in         NUMBER(5)
);

CREATE TABLE ADMISSION (
   Admission_id    NUMBER(3) PRIMARY KEY,
   Patient_id      NUMBER(3),
   Admission_date  DATE NOT NULL,
   Expected_op     CHAR(3),
   Admitted_by     NUMBER(3),
   Ward_code       CHAR(3),
   Discharge_date  DATE,
FOREIGN KEY (Patient_id) references PERSON(Person_id),
FOREIGN KEY (Expected_op) references OPERATION_TYPE(Op_code),
FOREIGN KEY (Admitted_by) references PERSON(Person_id),
FOREIGN KEY (Ward_code) references WARD(Ward_code)
);

CREATE TABLE OPERATION (
   Operation_id     NUMBER(3) PRIMARY KEY,
   Actual_op        CHAR(3),
   Admission_id     NUMBER(3),
   Op_date          DATE,
   Surgeon          NUMBER(3),
   Anaesthetist     NUMBER(3),
FOREIGN KEY (Surgeon) references PERSON(Person_id),
FOREIGN KEY (Anaesthetist) references PERSON(Person_id),
FOREIGN KEY (Actual_op) references OPERATION_TYPE(Op_code),
FOREIGN KEY (Admission_id) references ADMISSION(Admission_id)
);

CREATE TABLE OBSERVATION(
   Admission_id  NUMBER(3),
   Observ_date DATE,
   Observ_time   NUMBER(4),
   Observ_type   CHAR(10),
   Observ_value  NUMBER(4),
   Staff_id      NUMBER(3),
PRIMARY KEY (Admission_id,Observ_date,Observ_time,Observ_type),
FOREIGN KEY (Admission_id) references ADMISSION(Admission_id),
FOREIGN KEY (Staff_id) references STAFF(Person_id)
);


-- With test data

INSERT INTO person (Person_id, First_name, Surname) VALUES (1, 'First1', 'Last1');
INSERT INTO person (Person_id, First_name, Surname) VALUES (2, 'First2', 'Last2');

INSERT INTO ward (Ward_code, Daily_charge)  VALUES (1, 100);
INSERT INTO ward (Ward_code, Daily_charge)  VALUES (2, 120);

INSERT INTO admission (admission_id, patient_id, admission_date, discharge_date, ward_code) VALUES (1, 1, current_date, current_date, 1);
INSERT INTO admission (admission_id, patient_id, admission_date, discharge_date, ward_code) VALUES (2, 2, current_date, current_date, 2);


INSERT INTO observation (admission_id, observ_value, observ_type, Observ_date, Observ_time) VALUES (1, 1000, 'Temp', current_date, 1000);
INSERT INTO observation (admission_id, observ_value, observ_type, Observ_date, Observ_time) VALUES (1, 1100, 'Temp', current_date, 1100);
INSERT INTO observation (admission_id, observ_value, observ_type, Observ_date, Observ_time) VALUES (2, 2000, 'Temp', current_date, 2000);
INSERT INTO observation (admission_id, observ_value, observ_type, Observ_date, Observ_time) VALUES (2, 2100, 'Temp', current_date, 2100);

sql - 列出较早入院但未进行任何手术的出院患者

问题描述

解决方案

推荐阅读