问oracle查询获取已发布通行证的20家顶级机构

EN

SELECT gp.agent_id, a.agent_name, COUNT(*)
FROM eofficeuat.gatepass gp INNER JOIN 
     eofficeuat.cnf_agents a
     ON gp.agent_id = a.agent_id INNER JOIN
     eofficeuat.cardprintlog_user u
     ON gp.agent_id = u.agent_id
WHERE gp.issuedatetime BETWEN DATE '2019-09-28' AND DATE '2019-09-29'
GROUP BY gp.agent_id, a.agent_name
ORDER BY COUNT(*) DESC
FETCH FIRST 1 ROW ONLY;

SELECT *
FROM (SELECT gp.agent_id, a.agent_name, COUNT(*)
      FROM eofficeuat.gatepass gp INNER JOIN 
           eofficeuat.cnf_agents a
           ON gp.agent_id = a.agent_id INNER JOIN
           eofficeuat.cardprintlog_user u
           ON gp.agent_id = u.agent_id
      WHERE gp.issuedatetime BETWEEN DATE '2019-09-28' AND DATE '2019-09-29'
      GROUP BY gp.agent_id, a.agent_name
      ORDER BY COUNT(*) DESC
     ) a
WHERE rownum <= 20;

gp.issuedatetime >= DATE '2019-09-28' AND
gp.issuedatetime < DATE '2019-09-29'

gp.issuedatetime >= DATE '2019-09-28' AND
gp.issuedatetime < DATE '2019-09-30'

SELECT eofficeuat.gatepass.agent_id, eofficeuat.cnf_agents.agent_name, COUNT(1) as cnt
FROM eofficeuat.gatepass INNER JOIN 
     eofficeuat.cnf_agents
     ON eofficeuat.gatepass.agent_id = eofficeuat.cnf_agents.agent_id INNER JOIN
     eofficeuat.cardprintlog_user
     ON eofficeuat.gatepass.agent_id = eofficeuat.cardprintlog_user.agent_id
WHERE eofficeuat.gatepass.issuedatetime BETWEN DATE '2019-09-28' AND DATE '2019-09-29'
GROUP BY eofficeuat.gatepass.agent_id, eofficeuat.cnf_agents.agent_name
ORDER BY cnt DESC
FETCH FIRST 20 ROWS ONLY; -- this will fetch top 20 agents