类似

SELECT * FROM (

SELECT ROW_NUMBER() OVER(PARTITION BY gongsi ORDER BY zongshu DESC) [NUM_],* FROM biao where createdate>='2024-07-08'  and createdate<='2024-07-09' 

) T WHERE T.NUM_<=10

---------这样就行了。测试是可以的-------------------

select 

t2.* from (

select 

t1.*,

ROW_NUMBER() OVER(PARTITION BY t1.gongsi ORDER BY zongshu desc) AS RN

from (

SELECT

gongsi,

zhibiao,

avg(zongshu) as zongshu 

FROM biao 

where createdate>='2024-07-08'  

and createdate<='2024-07-09' 

group by gongsi,zhibiao ) t1

) t2

where t2.RN<=10

WITH RankedData AS (  

    SELECT   

        t1.gongsi,  

        t1.zhibiao,  

        ROW_NUMBER() OVER (PARTITION BY t1.gongsi ORDER BY t1.zongshu DESC) AS RN  

    FROM   

        (SELECT   

            AVG(zongshu) AS zongshu,   

            gongsi,   

            zhibiao   

        FROM   

            biao  

        WHERE   

            createdate >= '2024-07-08' AND createdate <= '2024-07-09'   

        GROUP BY   

            gongsi, zhibiao  

        ) t1  

)  

SELECT   

    gongsi,  

    zhibiao,  

    RN  

FROM   

    RankedData  

WHERE   

    RN <= 10;