oracle统计连续日期有记录的最大值

Question

1，有一张交易流水表，里面记录交易时间（pay_date）、交易人卡Id(cardId),需要统计，连续有交易流水的人，的前10名？
这条语句该怎么写？或者有其他的办法吗

Answer 1

SELECT * FROM (SELECT P_T.*, ROWNUM AS RN FROM
(SELECT C.CARD_ID,MAX(C.COUNTS) AS MAXDAYS,CARD.NAME AS customUserName,CARD.SALARY_NO
FROM (SELECT B.CARD_ID,B.DAYS,COUNT(B.CARD_ID) AS COUNTS
FROM (SELECT A.CARD_ID, A.PAY_DATE - ROWNUM AS DAYS
FROM (SELECT CARD_ID,TO_DATE(TO_CHAR(PAY_DATE, 'YYYYMMDD'), 'YYYYMMDD') AS PAY_DATE
FROM T_PM_PAY_${restaurantNo}
<![CDATA[ WHERE TO_CHAR(PAY_DATE, 'YYYY-MM-DD') <= '2016-12-31' AND CARD_ID!='1000000000']]>
GROUP BY CARD_ID, TO_CHAR(PAY_DATE, 'YYYYMMDD')
ORDER BY CARD_ID, TO_CHAR(PAY_DATE, 'YYYYMMDD')
) A
) B
GROUP BY B.CARD_ID, B.DAYS) C
LEFT JOIN T_UM_CARDINFO CARD

ON C.CARD_ID = CARD.CARD_ID AND CARD.ORG_ID=#{orgId}
GROUP BY C.CARD_ID ,CARD.NAME,CARD.SALARY_NO
ORDER BY MAXDAYS DESC, C.CARD_ID ASC
) P_T)
<![CDATA[ WHERE RN <=10]]>

重点注意那个rownum