如何查找自某个日期(例如:2018到当前日期)以来不同记录的计数?

6tr1vspr  于 2021-07-26  发布在  Java
关注(0)|答案(2)|浏览(421)

所以我最近刚开始研究甲骨文,我必须找到在医院至少住院两次的病人。
这是我工作的table。
表person:-

  1. CREATE TABLE PERSON
  2. (
  3. Person_id NUMBER(3) PRIMARY KEY,
  4. Surname VARCHAR2(20),
  5. First_name VARCHAR2(20),
  6. Sex CHAR(1),
  7. Birth_date DATE,
  8. Street VARCHAR2(40),
  9. Town CHAR(9),
  10. Postcode NUMBER(4),
  11. Next_of_kin NUMBER(3)
  12. );
  13. INSERT INTO PERSON VALUES (101,'Black','Barry','M','31/12/1959','11 High St.','Cooma',2630,102);
  14. INSERT INTO PERSON VALUES (102,'Black','Mary','F','11/1/1963','11 High St.','Cooma',2630,NULL);
  15. INSERT INTO PERSON VALUES (103,'Strathclyde','Albert','M','15/5/1955','3 The Mews','Hawthorne',3171,104);
  16. INSERT INTO PERSON VALUES (104,'Strathclyde','Alice','F','17/7/1955','3 The Mews','Hawthorne',3171,103);
  17. INSERT INTO PERSON VALUES (105,'Green','Gill','F','16/6/1966','124 Main St.','Young',2594,106);
  18. INSERT INTO PERSON VALUES (106,'Green','Graham','M','24/4/1967','124 Main St.','Young',2594,105);
  19. INSERT INTO PERSON VALUES (107,'Gray','Lesley','F','12/9/1972','130 Main St.','Young',2594,109);
  20. INSERT INTO PERSON VALUES (109,'Gray','John','M','14/4/1972','130 Main St.','Young',2594,107);
  21. INSERT INTO PERSON VALUES (110,'Samuelson','Thomas','M','1/1/1964','17 The Mews','Hawthorne',3171,NULL);
  22. INSERT INTO PERSON VALUES (111,'Abrahams','Mary','F','15/5/1967','2177A The Esplanade','Ivanhoe',3878,NULL);
  23. INSERT INTO PERSON VALUES (112,'Aumann','Monica','F','25/5/1955','29 The Esplanade','Ivanhoe',3878,NULL);
  24. INSERT INTO PERSON VALUES (113,'Brown','Melissa','F','8/8/1984','11 East St.','Cooma',2630,NULL);
  25. INSERT INTO PERSON VALUES (114,'Napier','Mary','F','1/1/1971','163 New Rd.','Henty',2658,NULL);
  26. INSERT INTO PERSON VALUES (115,'Nelson','Nigel','M','2/2/1972','165 Young Rd.','Temora',2666,NULL);
  27. INSERT INTO PERSON VALUES (116,'Newman','Olive','F','3/3/1973','21 Olympic Way','Henty',2658,NULL);
  28. INSERT INTO PERSON VALUES (117,'Gray','Lesley','M','31/12/1989','130 Andres St.','Young',2594,105);
  29. INSERT INTO PERSON VALUES (118,'Hon','Tasuku','M','13/3/1953','21 Silcon Height','Henty',2658,NULL);
  30. INSERT INTO PERSON VALUES (119,'Livingstone','Frank','M','3/3/2003','21 Sun Height','Henty',2658,122);
  31. INSERT INTO PERSON VALUES (120,'Giggle','Frank','M','23/3/1975','21 Albrige Close','Cooma',2630,121);
  32. INSERT INTO PERSON VALUES (121,'Giggle','Felicia','F','3/3/1980','21 Albrige Close','Cooma',2630,120);
  33. INSERT INTO PERSON VALUES (122,'Gigg','Frank Jr','M','13/3/2011','21 Stun Height','Henty',2658,123);
  34. INSERT INTO PERSON VALUES (123,'Gigg','Frances','F','12/12/2005','21 Stun Height','Henty',2658,122);
  35. INSERT INTO PERSON VALUES (124,'Smith','Buddy','M','11/12/1979','101 High St.','Cooma',2630,NULL);
  36. INSERT INTO PERSON VALUES (125,'Smith','Maxime','F','31/12/1979','101 High St.','Cooma',2630,124);
  37. INSERT INTO PERSON VALUES (126,'Smith','Issac','M','1/12/2007','101 High St.','Cooma',2630,124);
  38. INSERT INTO PERSON VALUES (127,'Smith','Ronny','M','3/12/2009','101 High St.','Cooma',2630,124);
  39. INSERT INTO PERSON VALUES (128,'Giggle','Fanny','F','3/12/2007','121 Close Rose','Hillo',2330,120);
  40. INSERT INTO PERSON VALUES (129,'Murad','Nadia','F','3/3/2000','121 Close Rose', 'Hillo',2330,130);
  41. INSERT INTO PERSON VALUES (130,'Murad','Tange','M','3/3/1999','7711 Albrige Close','Cooma',2630,NULL);
  42. INSERT INTO PERSON VALUES (131,'Rome','Paula','F','23/9/1965','21 Height Close','Cooma',2630,132);
  43. INSERT INTO PERSON VALUES (132,'Rome','Paul','M','13/3/1966','21 Height Close','Cooma',2630,NULL);
  44. INSERT INTO PERSON VALUES (133,'Rome','Fay','M','3/3/2017','21 Height Close','Cooma',2630,132);
  45. INSERT INTO PERSON VALUES (134,'Murad','Michelle','F','3/3/2001','1 Height Close','Cooma',2630,NULL);
  46. INSERT INTO PERSON VALUES (135,'Trump','Donald','M','13/3/1966','222 White House Avenue','Cooma',2630,NULL);
  47. INSERT INTO PERSON VALUES (136,'Trump','Melania','F','3/3/1992','222 White House Avenue','Cooma',2630,135);
  48. INSERT INTO PERSON VALUES (137,'Trump','Baron','M','3/3/2005','222 White House Avenue','Cooma',2630,135);
  49. INSERT INTO PERSON VALUES (138,'Johnson','Boris','M','23/9/1965','10 Downing Street','London',2999,NULL);
  50. INSERT INTO PERSON VALUES (139,'Cordeiro','Wayne','M','3/3/1965','777 Hawaii Close Rose', 'Hillo',7770, NULL);
  51. INSERT INTO PERSON VALUES (140,'Cordeiro','Wayne','M','3/3/1965','777 Hawaii Close Rose', 'Hillo',7770, NULL);

餐桌入场:-

  1. CREATE TABLE ADMISSION (
  2. Admission_id NUMBER(3) PRIMARY KEY,
  3. Patient_id NUMBER(3),
  4. Admission_date DATE NOT NULL,
  5. Expected_op CHAR(3),
  6. Admitted_by NUMBER(3),
  7. Ward_code CHAR(3),
  8. Discharge_date DATE,
  9. FOREIGN KEY (Patient_id) references PERSON(Person_id),
  10. FOREIGN KEY (Expected_op) references OPERATION_TYPE(Op_code),
  11. FOREIGN KEY (Admitted_by) references PERSON(Person_id),
  12. FOREIGN KEY (Ward_code) references WARD(Ward_code)
  13. );
  14. INSERT INTO ADMISSION VALUES (205,101,'2/2/2011','HB',114,'P','21/2/2011');
  15. INSERT INTO ADMISSION VALUES (275,101,'1/9/2010','HY',115,'L','1/11/2010');
  16. INSERT INTO ADMISSION VALUES (286,101,'3/5/2016','AR',116,'A','3/7/2016');
  17. INSERT INTO ADMISSION VALUES (303,101,'3/4/2018','LA',125,'F', '13/5/2018' );
  18. INSERT INTO ADMISSION VALUES (298,103,'23/1/2016','TS',114,'L','24/04/2016');
  19. INSERT INTO ADMISSION VALUES (299,103,'23/3/2016','AP',114,'L','23/4/2016');
  20. INSERT INTO ADMISSION VALUES (305,103,'23/4/2018','HT',125,'F','29/5/2018');
  21. INSERT INTO ADMISSION VALUES (321,103,'13/5/2018','AR',125,'F', '23/5/2018' );
  22. INSERT INTO ADMISSION VALUES (283,105,'3/12/2015','AR',116,'A','5/12/2015');
  23. INSERT INTO ADMISSION VALUES (278,105,'1/1/2011','HB',115,'P','30/1/2011');
  24. INSERT INTO ADMISSION VALUES (307,105,'3/4/2018','TS',125,'F', '13/5/2018');
  25. INSERT INTO ADMISSION VALUES (276,106,'24/8/2010','LA',114,'P','15/9/2010');
  26. INSERT INTO ADMISSION VALUES (287,106,'3/5/2016','AP',114,'A','3/6/2016');
  27. INSERT INTO ADMISSION VALUES (274,109,'1/9/2010','AP',114,'P','9/9/2010');
  28. INSERT INTO ADMISSION VALUES (288,109,'23/5/2016','LO',114,'F','3/07/2016');
  29. INSERT INTO ADMISSION VALUES (301,112,'13/4/2018','AP',125,'F','16/4/2018');
  30. INSERT INTO ADMISSION VALUES (304,112,'23/4/2019','LO',114,'L','23/5/2019');
  31. INSERT INTO ADMISSION VALUES (279,113,'3/9/2010','TS',115,'F','10/9/2010');
  32. INSERT INTO ADMISSION VALUES (284,113,'3/12/2015','HY',116,'A','03/01/2016');
  33. INSERT INTO ADMISSION VALUES (285,113,'3/5/2016','HT',116,'A','3/6/2016');
  34. INSERT INTO ADMISSION VALUES (300,113,'23/4/2018','AR',114,'L','25/6/2018');
  35. INSERT INTO ADMISSION VALUES (306,113,'13/8/2018','AP',125,'L', '13/9/2018');
  36. INSERT INTO ADMISSION VALUES (277,114,'20/9/2010','AP',115,'P','30/9/2010');
  37. INSERT INTO ADMISSION VALUES (289,115,'11/4/2016','TS',114,'L','3/6/2016');
  38. INSERT INTO ADMISSION VALUES (290,115,'5/5/2016','LO',114,'L','3/06/2016');
  39. INSERT INTO ADMISSION VALUES (308,115,'23/3/2018','AR',114,'L','25/3/2018');
  40. INSERT INTO ADMISSION VALUES (280,117,'3/9/2010','AP',115,'F','25/9/2010');
  41. INSERT INTO ADMISSION VALUES (281,117,'3/9/2014','HB',116,'A','21/9/2014');
  42. INSERT INTO ADMISSION VALUES (282,117,'3/12/2015','LA',116,'A','4/12/2015');
  43. INSERT INTO ADMISSION VALUES (309,126,'23/3/2018','TS',125,'L', '13/5/2018');
  44. INSERT INTO ADMISSION VALUES (310,127,'13/5/2018','AP',125,'L', '28/5/2018');
  45. INSERT INTO ADMISSION VALUES (311,124,'3/5/2018','LO',125,'A', '23/5/2018' );
  46. INSERT INTO ADMISSION VALUES (312,127,'21/6/2019','LO',125,'L','22/8/2019');
  47. INSERT INTO ADMISSION VALUES (313,124,'22/6/2019','AP',125,'A','22/7/2019');
  48. INSERT INTO ADMISSION VALUES (314,109,'21/6/2019','LO',125,'L','22/7/2019');
  49. INSERT INTO ADMISSION VALUES (315,126,'12/6/2019','AP',125,'A','22/9/2019');
  50. INSERT INTO ADMISSION VALUES (316,114,'22/7/2019','HB',125,'A','12/12/2019');
  51. INSERT INTO ADMISSION VALUES (318,128,'3/5/2019','LA',116,'A','4/6/2019');
  52. INSERT INTO ADMISSION VALUES (319,129,'23/3/2019','TS',125,'L', '13/4/2019');
  53. INSERT INTO ADMISSION VALUES (320,130,'3/5/2019','LA',116,'F','4/6/2019');
  54. INSERT INTO ADMISSION VALUES (328,119,'3/3/2019','TS',115,'N', '3/4/2019');
  55. INSERT INTO ADMISSION VALUES (322,132,'3/5/2019','LA',116,'A','4/6/2019');
  56. INSERT INTO ADMISSION VALUES (323,133,'23/3/2019','TS',125,'L', '13/4/2019');
  57. INSERT INTO ADMISSION VALUES (324,131,'3/5/2018','LA',116,'F','4/5/2018');
  58. INSERT INTO ADMISSION VALUES (325,118,'3/6/2019','TS',115,'P', '3/7/2019');
  59. INSERT INTO ADMISSION VALUES (326,102,'3/5/2018','LA',116,'F','4/5/2018');
  60. INSERT INTO ADMISSION VALUES (327,104,'3/6/2019','TS',115,'P', '3/7/2019');
  61. INSERT INTO ADMISSION VALUES (339,107,'3/6/2019','TS',115,'P', '3/7/2019');
  62. INSERT INTO ADMISSION VALUES (329,110,'3/6/2019','TS',115,'P', '3/7/2019');
  63. INSERT INTO ADMISSION VALUES (330,111,'3/6/2019','TS',115,'P', '3/7/2019');
  64. INSERT INTO ADMISSION VALUES (331,116,'3/6/2019','TS',115,'P', '3/7/2019');
  65. INSERT INTO ADMISSION VALUES (332,121,'21/6/2019','TS',125,'L','22/7/2019');
  66. INSERT INTO ADMISSION VALUES (333,123,'22/7/2019','AP',125,'A','22/9/2019');
  67. INSERT INTO ADMISSION VALUES (334,134,'21/8/2019','AP',115,'L','22/12/2019');
  68. INSERT INTO ADMISSION VALUES (335,128,'12/8/2019','AP',115,'A','22/10/2019');
  69. INSERT INTO ADMISSION VALUES (336,125,'22/7/2018','HB',115,'A','22/8/2018');
  70. INSERT INTO ADMISSION VALUES (337,120,'21/8/2019','AP',116,'L','22/10/2019');
  71. INSERT INTO ADMISSION VALUES (338,130,'22/7/2019','AP',125,'N','22/8/2019');
  72. INSERT INTO ADMISSION VALUES (340,131,'22/8/2019','AP',125,'N','22/10/2019');
  73. INSERT INTO ADMISSION VALUES (341,122,'22/8/2019','AP',125,'N','22/11/2019');
  74. INSERT INTO ADMISSION VALUES (404,112,'23/4/2020','CO',135,'C',NULL);
  75. INSERT INTO ADMISSION VALUES (400,113,'23/4/2020','CO',135,'C',NULL);
  76. INSERT INTO ADMISSION VALUES (406,115,'13/2/2020','CO',135,'C',NULL);
  77. INSERT INTO ADMISSION VALUES (432,121,'21/4/2020','CO',135,'C',NULL);
  78. INSERT INTO ADMISSION VALUES (433,123,'22/4/2020','CO',138,'C',NULL);
  79. INSERT INTO ADMISSION VALUES (434,134,'21/4/2020','CO',138,'C',NULL);
  80. INSERT INTO ADMISSION VALUES (435,128,'12/4/2020','CO',138,'C',NULL);
  81. INSERT INTO ADMISSION VALUES (412,127,'21/4/2020','CO',138,'C',NULL);
  82. INSERT INTO ADMISSION VALUES (414,109,'21/4/2020','CO',138,'C',NULL);
  83. INSERT INTO ADMISSION VALUES (415,126,'12/4/2020','CO',135,'C',NULL);
  84. INSERT INTO ADMISSION VALUES (416,114,'22/3/2020','CO',135,'C',NULL);
  85. INSERT INTO ADMISSION VALUES (437,120,'21/2/2020','CO',136,'C',NULL);
  86. INSERT INTO ADMISSION VALUES (438,130,'22/2/2020','CO',135,'C',NULL);
  87. INSERT INTO ADMISSION VALUES (440,131,'22/2/2020','CO',135,'C',NULL);
  88. INSERT INTO ADMISSION VALUES (441,122,'22/2/2020','CO',135,'C',NULL);
  89. INSERT INTO ADMISSION VALUES (479,118,'3/4/2020','CO',135,'C',NULL);
  90. INSERT INTO ADMISSION VALUES (484,111,'3/3/2020','CO',136,'C',NULL);
  91. INSERT INTO ADMISSION VALUES (485,116,'3/3/2020','CO',136,'C',NULL);
  92. INSERT INTO ADMISSION VALUES (489,119,'11/4/2020','CO',136,'C',NULL);
  93. INSERT INTO ADMISSION VALUES (490,139,'11/4/2020','AP',125,'F',NULL);
  94. INSERT INTO ADMISSION VALUES (491,140,'21/4/2020','AP',138,'F',NULL);

我以为使用子查询会管用

  1. SELECT DISTINCT p.First_name ||' '|| p.Surname AS "Full Name",
  2. CONCAT(CONCAT(CONCAT(p.Street,', '),p.Town), p.Postcode) AS "Address",
  3. TRUNC((SYSDATE-p.Birth_date)/365.25) AS Age
  4. FROM PERSON p JOIN ADMISSION a ON p.Person_id = a.Patient_id
  5. WHERE p.Person_id IN(
  6. SELECT Patient_id
  7. FROM ADMISSION
  8. GROUP BY Patient_id
  9. HAVING (COUNT(Patient_id)>=2)
  10. )
  11. AND a.Admission_date>='1/1/2018';

但我遇到的错误是,其中一名患者“巴里·布莱克”出现在输出中,尽管自2018年以来只入院过一次。
输出表
非常感谢您的帮助。

hl0ma9xz

hl0ma9xz1#

此查询以预期格式提供结果,

  1. select first_name ||' '|| surname as full_name, street ||' '|| town ||' '|| postcode
  2. as address, to_char(birth_date, 'YYYY') - to_char(sysdate, 'YYYY') as age
  3. from person;

此查询提供患者至少入院两次的结果,

  1. select first_name ||' '|| surname as full_name, street ||' '|| town ||' '|| postcode
  2. as address, to_char(birth_date, 'YYYY') - to_char(sysdate, 'YYYY') as age
  3. from person where id in
  4. (select id from
  5. (select a.id, count(1) from person p
  6. inner join admission a
  7. on (p.persion_id = a.patient_id)
  8. group by a.id having count(1) >= 2));
bfhwhh0e

bfhwhh0e2#

如果您想删除患者“barry black”,因为他自2018年以来只入院一次,那么我们可以通过子查询对组应用筛选,以便仅限定自2018年以来多次就诊的患者。
请注意,根据相同的标准,这将取消“gill green”患者id 105的资格,该患者id 105也出现在您的输出结果中,而不仅仅是“barry black”。

  1. alter session set NLS_DATE_FORMAT = 'DD/MM/YYYY';
  2. SELECT
  3. DISTINCT p.First_name ||' '|| p.Surname AS "Full Name",
  4. CONCAT(CONCAT(CONCAT(p.Street,', '),p.Town), p.Postcode) AS "Address",
  5. TRUNC((SYSDATE-p.Birth_date)/365.25) AS Age
  6. FROM PERSON p JOIN ADMISSION a ON p.Person_id = a.Patient_id
  7. WHERE p.Person_id IN (
  8. SELECT Patient_id
  9. FROM ADMISSION
  10. WHERE Admission_date>='1/1/2018'
  11. GROUP BY Patient_id
  12. HAVING (COUNT(Patient_id)>=2)
  13. )
  14. AND a.Admission_date>='1/1/2018'
  15. order by 1,2,3
  16. ;
展开查看全部

相关问题