sql - Oracle查询以获取之前已取消的总发行卡号(Oracle query for get total issue card number which was canceled before)

Question

I wanted to show up total number of agent card number reissue which agents canceled the card before.

(我想显示代理商之前重新发行的代理商卡号的总数。)

I have three types of card status READY,CANCEL,BLOCK.

(我的卡状态为READY，CANCEL，BLOCK三种。)

So I only need to get READY status card as reissue.

(因此，我只需要重新获得就绪状态卡即可。)

How can I get agents which card canceled before and reissued between the date 23/11/2019 to 25/11/2019.

(我如何获得代理商在23/11/2019至25/11/2019之间取消过信用卡并已重新发行的代理商。)

Here is below my query to get all cards as canceled

(这是在我的查询下方，以取消所有卡)

SELECT
eofficeuat.cardprintlog_user.agent_id,
eofficeuat.cardprintlog_user.cardnumber,
eofficeuat.cardprintlog_user.cardtype,
eofficeuat.cardprintlog_user.dateofissue,
eofficeuat.cardprintlog_user.cardstatus
FROM
eofficeuat.cardprintlog_user
INNER JOIN eofficeuat.cnf_agents ON eofficeuat.cardprintlog_user.agent_id = 
eofficeuat.cnf_agents.agent_id
INNER JOIN eofficeuat.agent_types ON eofficeuat.cnf_agents.agent_type_id = 
eofficeuat.agent_types.agent_type_id
where eofficeuat.agent_types.agent_type_id='12'

and eofficeuat.cardprintlog_user.cardstatus='CANCEL'

can you please help me for this?

(你能帮我吗？)

  ask by mdkamrul translate from so

Answer 1

Seems you need LEAD() and ROW_NUMBER() analytic functions :

(似乎您需要LEAD()和ROW_NUMBER()分析函数：)

SELECT agent_id,cardnumber,cardtype,dateofissue,cardstatus
  FROM
  (
   SELECT cu.agent_id,cu.cardnumber,cu.cardtype,cu.dateofissue,cu.cardstatus,
          LEAD(cu.cardstatus,1) OVER (PARTITION BY cu.cardnumber ORDER BY cu.dateofissue DESC) as pre_cardstatus,
          ROW_NUMBER() OVER (PARTITION BY cu.cardnumber ORDER BY cu.dateofissue DESC) as rn
     FROM eofficeuat.cardprintlog_user cu
     JOIN eofficeuat.cnf_agents ca ON cu.agent_id = ca.agent_id
     JOIN eofficeuat.agent_types at ON ca.agent_type_id = at.agent_type_id
    WHERE at.agent_type_id='12'       
      AND dateofissue BETWEEN date'2019-11-23' AND date'2019-11-25'
   )
  WHERE pre_cardstatus = 'CANCEL' 
    AND cardstatus='READY' 
    AND rn = 1