所以我最近才开始使用Oracle，我必须基本上找到在给定医院至少两次入院的患者。
以下是我正在使用的表格。
桌人：-
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(9),
Postcode NUMBER(4),
Next_of_kin NUMBER(3)
);
INSERT INTO PERSON VALUES (101,'Black','Barry','M','31/12/1959','11 High St.','Cooma',2630,102);
INSERT INTO PERSON VALUES (102,'Black','Mary','F','11/1/1963','11 High St.','Cooma',2630,NULL);
INSERT INTO PERSON VALUES (103,'Strathclyde','Albert','M','15/5/1955','3 The Mews','Hawthorne',3171,104);
INSERT INTO PERSON VALUES (104,'Strathclyde','Alice','F','17/7/1955','3 The Mews','Hawthorne',3171,103);
INSERT INTO PERSON VALUES (105,'Green','Gill','F','16/6/1966','124 Main St.','Young',2594,106);
INSERT INTO PERSON VALUES (106,'Green','Graham','M','24/4/1967','124 Main St.','Young',2594,105);
INSERT INTO PERSON VALUES (107,'Gray','Lesley','F','12/9/1972','130 Main St.','Young',2594,109);
INSERT INTO PERSON VALUES (109,'Gray','John','M','14/4/1972','130 Main St.','Young',2594,107);
INSERT INTO PERSON VALUES (110,'Samuelson','Thomas','M','1/1/1964','17 The Mews','Hawthorne',3171,NULL);
INSERT INTO PERSON VALUES (111,'Abrahams','Mary','F','15/5/1967','2177A The Esplanade','Ivanhoe',3878,NULL);
INSERT INTO PERSON VALUES (112,'Aumann','Monica','F','25/5/1955','29 The Esplanade','Ivanhoe',3878,NULL);
INSERT INTO PERSON VALUES (113,'Brown','Melissa','F','8/8/1984','11 East St.','Cooma',2630,NULL);
INSERT INTO PERSON VALUES (114,'Napier','Mary','F','1/1/1971','163 New Rd.','Henty',2658,NULL);
INSERT INTO PERSON VALUES (115,'Nelson','Nigel','M','2/2/1972','165 Young Rd.','Temora',2666,NULL);
INSERT INTO PERSON VALUES (116,'Newman','Olive','F','3/3/1973','21 Olympic Way','Henty',2658,NULL);
INSERT INTO PERSON VALUES (117,'Gray','Lesley','M','31/12/1989','130 Andres St.','Young',2594,105);
INSERT INTO PERSON VALUES (118,'Hon','Tasuku','M','13/3/1953','21 Silcon Height','Henty',2658,NULL);
INSERT INTO PERSON VALUES (119,'Livingstone','Frank','M','3/3/2003','21 Sun Height','Henty',2658,122);
INSERT INTO PERSON VALUES (120,'Giggle','Frank','M','23/3/1975','21 Albrige Close','Cooma',2630,121);
INSERT INTO PERSON VALUES (121,'Giggle','Felicia','F','3/3/1980','21 Albrige Close','Cooma',2630,120);
INSERT INTO PERSON VALUES (122,'Gigg','Frank Jr','M','13/3/2011','21 Stun Height','Henty',2658,123);
INSERT INTO PERSON VALUES (123,'Gigg','Frances','F','12/12/2005','21 Stun Height','Henty',2658,122);
INSERT INTO PERSON VALUES (124,'Smith','Buddy','M','11/12/1979','101 High St.','Cooma',2630,NULL);
INSERT INTO PERSON VALUES (125,'Smith','Maxime','F','31/12/1979','101 High St.','Cooma',2630,124);
INSERT INTO PERSON VALUES (126,'Smith','Issac','M','1/12/2007','101 High St.','Cooma',2630,124);
INSERT INTO PERSON VALUES (127,'Smith','Ronny','M','3/12/2009','101 High St.','Cooma',2630,124);
INSERT INTO PERSON VALUES (128,'Giggle','Fanny','F','3/12/2007','121 Close Rose','Hillo',2330,120);
INSERT INTO PERSON VALUES (129,'Murad','Nadia','F','3/3/2000','121 Close Rose', 'Hillo',2330,130);
INSERT INTO PERSON VALUES (130,'Murad','Tange','M','3/3/1999','7711 Albrige Close','Cooma',2630,NULL);
INSERT INTO PERSON VALUES (131,'Rome','Paula','F','23/9/1965','21 Height Close','Cooma',2630,132);
INSERT INTO PERSON VALUES (132,'Rome','Paul','M','13/3/1966','21 Height Close','Cooma',2630,NULL);
INSERT INTO PERSON VALUES (133,'Rome','Fay','M','3/3/2017','21 Height Close','Cooma',2630,132);
INSERT INTO PERSON VALUES (134,'Murad','Michelle','F','3/3/2001','1 Height Close','Cooma',2630,NULL);
INSERT INTO PERSON VALUES (135,'Trump','Donald','M','13/3/1966','222 White House Avenue','Cooma',2630,NULL);
INSERT INTO PERSON VALUES (136,'Trump','Melania','F','3/3/1992','222 White House Avenue','Cooma',2630,135);
INSERT INTO PERSON VALUES (137,'Trump','Baron','M','3/3/2005','222 White House Avenue','Cooma',2630,135);
INSERT INTO PERSON VALUES (138,'Johnson','Boris','M','23/9/1965','10 Downing Street','London',2999,NULL);
INSERT INTO PERSON VALUES (139,'Cordeiro','Wayne','M','3/3/1965','777 Hawaii Close Rose', 'Hillo',7770, NULL);
INSERT INTO PERSON VALUES (140,'Cordeiro','Wayne','M','3/3/1965','777 Hawaii Close Rose', 'Hillo',7770, NULL);
表入场费：-
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)
);
INSERT INTO ADMISSION VALUES (205,101,'2/2/2011','HB',114,'P','21/2/2011');
INSERT INTO ADMISSION VALUES (275,101,'1/9/2010','HY',115,'L','1/11/2010');
INSERT INTO ADMISSION VALUES (286,101,'3/5/2016','AR',116,'A','3/7/2016');
INSERT INTO ADMISSION VALUES (303,101,'3/4/2018','LA',125,'F', '13/5/2018' );
INSERT INTO ADMISSION VALUES (298,103,'23/1/2016','TS',114,'L','24/04/2016');
INSERT INTO ADMISSION VALUES (299,103,'23/3/2016','AP',114,'L','23/4/2016');
INSERT INTO ADMISSION VALUES (305,103,'23/4/2018','HT',125,'F','29/5/2018');
INSERT INTO ADMISSION VALUES (321,103,'13/5/2018','AR',125,'F', '23/5/2018' );
INSERT INTO ADMISSION VALUES (283,105,'3/12/2015','AR',116,'A','5/12/2015');
INSERT INTO ADMISSION VALUES (278,105,'1/1/2011','HB',115,'P','30/1/2011');
INSERT INTO ADMISSION VALUES (307,105,'3/4/2018','TS',125,'F', '13/5/2018');
INSERT INTO ADMISSION VALUES (276,106,'24/8/2010','LA',114,'P','15/9/2010');
INSERT INTO ADMISSION VALUES (287,106,'3/5/2016','AP',114,'A','3/6/2016');
INSERT INTO ADMISSION VALUES (274,109,'1/9/2010','AP',114,'P','9/9/2010');
INSERT INTO ADMISSION VALUES (288,109,'23/5/2016','LO',114,'F','3/07/2016');
INSERT INTO ADMISSION VALUES (301,112,'13/4/2018','AP',125,'F','16/4/2018');
INSERT INTO ADMISSION VALUES (304,112,'23/4/2019','LO',114,'L','23/5/2019');
INSERT INTO ADMISSION VALUES (279,113,'3/9/2010','TS',115,'F','10/9/2010');
INSERT INTO ADMISSION VALUES (284,113,'3/12/2015','HY',116,'A','03/01/2016');
INSERT INTO ADMISSION VALUES (285,113,'3/5/2016','HT',116,'A','3/6/2016');
INSERT INTO ADMISSION VALUES (300,113,'23/4/2018','AR',114,'L','25/6/2018');
INSERT INTO ADMISSION VALUES (306,113,'13/8/2018','AP',125,'L', '13/9/2018');
INSERT INTO ADMISSION VALUES (277,114,'20/9/2010','AP',115,'P','30/9/2010');
INSERT INTO ADMISSION VALUES (289,115,'11/4/2016','TS',114,'L','3/6/2016');
INSERT INTO ADMISSION VALUES (290,115,'5/5/2016','LO',114,'L','3/06/2016');
INSERT INTO ADMISSION VALUES (308,115,'23/3/2018','AR',114,'L','25/3/2018');
INSERT INTO ADMISSION VALUES (280,117,'3/9/2010','AP',115,'F','25/9/2010');
INSERT INTO ADMISSION VALUES (281,117,'3/9/2014','HB',116,'A','21/9/2014');
INSERT INTO ADMISSION VALUES (282,117,'3/12/2015','LA',116,'A','4/12/2015');
INSERT INTO ADMISSION VALUES (309,126,'23/3/2018','TS',125,'L', '13/5/2018');
INSERT INTO ADMISSION VALUES (310,127,'13/5/2018','AP',125,'L', '28/5/2018');
INSERT INTO ADMISSION VALUES (311,124,'3/5/2018','LO',125,'A', '23/5/2018' );
INSERT INTO ADMISSION VALUES (312,127,'21/6/2019','LO',125,'L','22/8/2019');
INSERT INTO ADMISSION VALUES (313,124,'22/6/2019','AP',125,'A','22/7/2019');
INSERT INTO ADMISSION VALUES (314,109,'21/6/2019','LO',125,'L','22/7/2019');
INSERT INTO ADMISSION VALUES (315,126,'12/6/2019','AP',125,'A','22/9/2019');
INSERT INTO ADMISSION VALUES (316,114,'22/7/2019','HB',125,'A','12/12/2019');
INSERT INTO ADMISSION VALUES (318,128,'3/5/2019','LA',116,'A','4/6/2019');
INSERT INTO ADMISSION VALUES (319,129,'23/3/2019','TS',125,'L', '13/4/2019');
INSERT INTO ADMISSION VALUES (320,130,'3/5/2019','LA',116,'F','4/6/2019');
INSERT INTO ADMISSION VALUES (328,119,'3/3/2019','TS',115,'N', '3/4/2019');
INSERT INTO ADMISSION VALUES (322,132,'3/5/2019','LA',116,'A','4/6/2019');
INSERT INTO ADMISSION VALUES (323,133,'23/3/2019','TS',125,'L', '13/4/2019');
INSERT INTO ADMISSION VALUES (324,131,'3/5/2018','LA',116,'F','4/5/2018');
INSERT INTO ADMISSION VALUES (325,118,'3/6/2019','TS',115,'P', '3/7/2019');
INSERT INTO ADMISSION VALUES (326,102,'3/5/2018','LA',116,'F','4/5/2018');
INSERT INTO ADMISSION VALUES (327,104,'3/6/2019','TS',115,'P', '3/7/2019');
INSERT INTO ADMISSION VALUES (339,107,'3/6/2019','TS',115,'P', '3/7/2019');
INSERT INTO ADMISSION VALUES (329,110,'3/6/2019','TS',115,'P', '3/7/2019');
INSERT INTO ADMISSION VALUES (330,111,'3/6/2019','TS',115,'P', '3/7/2019');
INSERT INTO ADMISSION VALUES (331,116,'3/6/2019','TS',115,'P', '3/7/2019');
INSERT INTO ADMISSION VALUES (332,121,'21/6/2019','TS',125,'L','22/7/2019');
INSERT INTO ADMISSION VALUES (333,123,'22/7/2019','AP',125,'A','22/9/2019');
INSERT INTO ADMISSION VALUES (334,134,'21/8/2019','AP',115,'L','22/12/2019');
INSERT INTO ADMISSION VALUES (335,128,'12/8/2019','AP',115,'A','22/10/2019');
INSERT INTO ADMISSION VALUES (336,125,'22/7/2018','HB',115,'A','22/8/2018');
INSERT INTO ADMISSION VALUES (337,120,'21/8/2019','AP',116,'L','22/10/2019');
INSERT INTO ADMISSION VALUES (338,130,'22/7/2019','AP',125,'N','22/8/2019');
INSERT INTO ADMISSION VALUES (340,131,'22/8/2019','AP',125,'N','22/10/2019');
INSERT INTO ADMISSION VALUES (341,122,'22/8/2019','AP',125,'N','22/11/2019');
INSERT INTO ADMISSION VALUES (404,112,'23/4/2020','CO',135,'C',NULL);
INSERT INTO ADMISSION VALUES (400,113,'23/4/2020','CO',135,'C',NULL);
INSERT INTO ADMISSION VALUES (406,115,'13/2/2020','CO',135,'C',NULL);
INSERT INTO ADMISSION VALUES (432,121,'21/4/2020','CO',135,'C',NULL);
INSERT INTO ADMISSION VALUES (433,123,'22/4/2020','CO',138,'C',NULL);
INSERT INTO ADMISSION VALUES (434,134,'21/4/2020','CO',138,'C',NULL);
INSERT INTO ADMISSION VALUES (435,128,'12/4/2020','CO',138,'C',NULL);
INSERT INTO ADMISSION VALUES (412,127,'21/4/2020','CO',138,'C',NULL);
INSERT INTO ADMISSION VALUES (414,109,'21/4/2020','CO',138,'C',NULL);
INSERT INTO ADMISSION VALUES (415,126,'12/4/2020','CO',135,'C',NULL);
INSERT INTO ADMISSION VALUES (416,114,'22/3/2020','CO',135,'C',NULL);
INSERT INTO ADMISSION VALUES (437,120,'21/2/2020','CO',136,'C',NULL);
INSERT INTO ADMISSION VALUES (438,130,'22/2/2020','CO',135,'C',NULL);
INSERT INTO ADMISSION VALUES (440,131,'22/2/2020','CO',135,'C',NULL);
INSERT INTO ADMISSION VALUES (441,122,'22/2/2020','CO',135,'C',NULL);
INSERT INTO ADMISSION VALUES (479,118,'3/4/2020','CO',135,'C',NULL);
INSERT INTO ADMISSION VALUES (484,111,'3/3/2020','CO',136,'C',NULL);
INSERT INTO ADMISSION VALUES (485,116,'3/3/2020','CO',136,'C',NULL);
INSERT INTO ADMISSION VALUES (489,119,'11/4/2020','CO',136,'C',NULL);
INSERT INTO ADMISSION VALUES (490,139,'11/4/2020','AP',125,'F',NULL);
INSERT INTO ADMISSION VALUES (491,140,'21/4/2020','AP',138,'F',NULL);
我认为使用子查询会起作用
SELECT DISTINCT p.First_name ||' '|| p.Surname AS "Full Name",
CONCAT(CONCAT(CONCAT(p.Street,', '),p.Town), p.Postcode) AS "Address",
TRUNC((SYSDATE-p.Birth_date)/365.25) AS Age
FROM PERSON p JOIN ADMISSION a ON p.Person_id = a.Patient_id
WHERE p.Person_id IN(
SELECT Patient_id
FROM ADMISSION
GROUP BY Patient_id
HAVING (COUNT(Patient_id)>=2)
)
AND a.Admission_date>='1/1/2018';
但我遇到的错误是，其中一名患者"巴里·布莱克"出现在输出中，尽管自 2018 年以来只入院过一次。
输出表
非常感谢任何帮助。
此查询以预期格式提供结果，
select first_name ||' '|| surname as full_name, street ||' '|| town ||' '|| postcode
as address, to_char(birth_date, 'YYYY') - to_char(sysdate, 'YYYY') as age
from person;
此查询为您提供患者至少入院两次的结果，
select first_name ||' '|| surname as full_name, street ||' '|| town ||' '|| postcode
as address, to_char(birth_date, 'YYYY') - to_char(sysdate, 'YYYY') as age
from person where id in
(select id from
(select a.id, count(1) from person p
inner join admission a
on (p.persion_id = a.patient_id)
group by a.id having count(1) >= 2));
如果您想删除患者"Barry Black"，因为他自 2018 年以来仅入院一次，那么我们可以按子查询对组应用过滤器，以便仅限定自 2018 年以来多次就诊的患者。
请注意，根据相同的标准，这会取消输出结果中存在的"Gill Green"患者 ID 105 的资格，而不仅仅是"Barry Black"。
alter session set NLS_DATE_FORMAT = 'DD/MM/YYYY';
SELECT
DISTINCT p.First_name ||' '|| p.Surname AS "Full Name",
CONCAT(CONCAT(CONCAT(p.Street,', '),p.Town), p.Postcode) AS "Address",
TRUNC((SYSDATE-p.Birth_date)/365.25) AS Age
FROM PERSON p JOIN ADMISSION a ON p.Person_id = a.Patient_id
WHERE p.Person_id IN (
SELECT Patient_id
FROM ADMISSION
WHERE Admission_date>='1/1/2018'
GROUP BY Patient_id
HAVING (COUNT(Patient_id)>=2)
)
AND a.Admission_date>='1/1/2018'
order by 1,2,3
;