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

2018-01-17 13:13:23来源:cnblogs.com作者:row_number() over(partition 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;  
第七城市

微信扫一扫

第七城市微信公众平台