如何从patient_id中找到最常用的数字

Question

要从中检索数据的表的图像：

REM准考表

插入进入值(205,101，'2/2/2011'，'HB',114，'P'，'21/2/2011')；

Answer 1

什么是“最频繁的数字”？入院人数最多的病人是谁？如果是这样，那么

select patient_id
from (select patient_id,
             rank() over (order by count(*) desc) rnk
      from admission
      group by patient_id
     )
where rnk = 1;

Answer 2

计数group by patient_id并仅使用FETCH FIRST 1行：

select count(*) cnt, patient_id 
  from admission group by patient_id 
order by cnt desc FETCH FIRST 1 ROWS ONLY;

如果您有Oracle < 12C R1，请使用rownum with subquery：

select *
from  
(select count(*) cnt, patient_id 
      from admission group by patient_id 
    order by cnt desc ) 
where ROWNUM = 1;

Answer 3

在这两种情况下，如果只需要patient_id，则可以从select中删除count(*)作为patient_max

Oracle

SELECT * FROM (
 SELECT patient_id,count(*) AS patient_max
           FROM admission 
           GROUP BY patient_id 
           ORDER BY count(*) DESC
 ) 
 WHERE ROWNUM <= 1;

Postgresq

 SELECT patient_id,count(*) AS patient_max
           FROM admission 
           GROUP BY patient_id 
           ORDER BY count(*) DESC 
           LIMIT 1;