LeetCode（数据库）- 每次访问的交易次数

--解决方案(1)
SELECT*FROM(
SELECTt5.rnbAStransactions_count, IFNULL(visits_count, 0) ASvisits_countFROM    (
SELECT0ASrnbUNIONSELECTROW_NUMBER() OVER () ASrnbFROMTransactions    ) t5LEFTJOIN    (
SELECTcntAStransactions_count            ,COUNT(user_id) ASvisits_countFROM        (
SELECTt1.user_id, COUNT(t2.amount) AScntFROMVisitst1LEFTJOINTransactionst2ONt1.user_id=t2.user_idANDt1.visit_date=t2.transaction_dateGROUPBYuser_id, visit_date        ) t3GROUPBYcnt    ) t4ONt5.rnb=t4.transactions_count) t6WHEREtransactions_count<= (
SELECTCOUNT(t2.amount) AScntFROMVisitst1LEFTJOINTransactionst2ONt1.user_id=t2.user_idANDt1.visit_date=t2.transaction_dateGROUPBYt1.user_id, visit_dateORDERBYcntDESCLIMIT1)
;
--解决方案(2)
#计算每个用户每日期的交易次数WITHt1AS(SELECTuser_id, transaction_date, COUNT(*) cntFROMTransactionsGROUPBYuser_id, transaction_date),
#计算每种交易次数对应的用户数(包含交易次数为0的情况)
t2AS(SELECT0transactions_count, SUM(IF(t1.cntISNULL, 1, 0)) visits_countFROMVisitsvLEFTJOINt1ONv.user_id=t1.user_idANDv.Visit_date=t1.transaction_dateUNIONALLSELECTcnt, COUNT(*) FROMt1GROUPBYcnt),
#生成一张最多Transactions.length条记录的序数表t3AS(SELECTCAST(@i :=@i+1ASSIGNED) transactions_count, maxnFROM (SELECT@i:=-1, MAX(transactions_count) maxnFROMt2) initLEFTJOINTransactionsON1=1)
#为中途没有的交易次数种类填充SELECTt3.transactions_count, IFNULL(t2.visits_count, 0) visits_countFROMt3LEFTJOINt2ONt3.transactions_count=t2.transactions_countWHEREt3.transactions_count<=maxn