MYSQL-實現sqlserver- row_number() over(partition by order by) 分組排序功能

標籤：

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;

 

MYSQL-實現sqlserver- row_number() over(partition by order by) 分組排序功能