2022-01-24开窗函数

with t1 as (select car_logo,count(1) cou from
vehicle_identification_info_new a where car_logo !=’’
group by car_logo order by cou desc limit 50) ,
t2 as ( select t1.car_logo, b.,row_number() over (partition by t1.car_logo)rn from t1
left join vehicle_identification_info_new b on
t1.car_logo = b.car_logo where b.create_time >=’2021-12-01’)
select from t2 where rn <= 40

简单的说row_number()从1开始，为每一条分组记录返回一个数字，这里的ROW_NUMBER() OVER (ORDER BY xlh DESC) 是先把xlh列降序，再为降序以后的没条xlh记录返回一个序号。
查询结果需要存在一张表，单独再取一次