web | Java

订单表order,字段有:order_id(订单ID), user_id(用户ID),amount(金额), pay_datetime(付费时间),channel_id(渠道ID),dt(分区字段) 查询dt=‘2021-08-04‘里每个渠道的金额最大3笔订单

千锋教育

select channel_id, order_id, amount, rank from ( select channel_id, order_id, amount, rank() over(partition by channel_id order by amount desc) `rank`


from order where dt = '2021-08-04' group by channel_id,order_id,amount ) t1 where t1.rank <= 3