欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

MYSQL-实现sqlserver- row_number() over(partition by order by) 分组排序功能

程序员文章站 2022-04-10 15:33:08
sqlserver: with Result as ( select SUM(F_DayValue) AS F_Value,F_ZZ_ttBuildID,F_EnergyItemCode from T_EC_EnergyItemDayResult where F_EnergyItemCode lik... ......
sqlserver:

with Result as
    (
        select SUM(F_DayValue) AS F_Value,F_ZZ_ttBuildID,F_EnergyItemCode 
        from T_EC_EnergyItemDayResult        
        where F_EnergyItemCode like '%000'
        and F_StartDay>=@ldStartDate and F_StartDay<=@ldEndDate
        and F_ZZ_ttBuildID IN (select F_BuildID from T_BD_BuildBaseInfo)
        group by F_ZZ_ttBuildID,F_EnergyItemCode
    )
    select a.F_Value,a.F_ZZ_ttBuildID,b.F_BuildName,a.F_EnergyItemCode,
    ROW_NUMBER() over(partition by a.F_EnergyItemCode order by a.F_Value desc) as nsort 
    from Result a
    left join T_BD_BuildBaseInfo b on a.F_ZZ_ttBuildID=b.F_BuildID

 

mysql:

 
CREATE TEMPORARY TABLE IF NOT EXISTS Result
    (
     select SUM(F_DayValue) AS F_Value,F_ZZ_ttBuildID,F_EnergyItemCode 
        from T_EC_EnergyItemDayResult        
        where F_EnergyItemCode like '%000'
        and F_StartDay>=V_ldStartDate and F_StartDay<=V_ldEndDate
        and F_ZZ_ttBuildID IN (select F_BuildID from T_BD_BuildBaseInfo)
        group by F_ZZ_ttBuildID,F_EnergyItemCode
    );
CREATE TEMPORARY TABLE IF NOT EXISTS TMP01
    (
     select a.F_Value,a.F_ZZ_ttBuildID,b.F_BuildName,a.F_EnergyItemCode
    from Result a 
    left join T_BD_BuildBaseInfo b on a.F_ZZ_ttBuildID=b.F_BuildID
    );
 select F_Value,F_ZZ_ttBuildID,F_BuildName,F_EnergyItemCode,nsort from (
 select heyf_tmp.F_Value,heyf_tmp.F_ZZ_ttBuildID,heyf_tmp.F_BuildName,heyf_tmp.F_EnergyItemCode,@rownum
    :=@rownum+1 ,
 if(@pdept=heyf_tmp.F_EnergyItemCode,@rank:=@rank+1,@rank:=1) as nsort,
 @pdept:=heyf_tmp.F_EnergyItemCode
 from (
 select F_Value,F_ZZ_ttBuildID,F_BuildName,F_EnergyItemCode from TMP01 order by F_EnergyItemCode ASC 
    ,F_Value desc
) heyf_tmp ,(select @rownum :=0 , @pdept := null ,@rank:=0) a) T;