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;
上一篇: vue中设置height:100%无效的问题及解决方法
下一篇: MySQL备份还原
推荐阅读
-
oracle sum(col1) over(partition by col2 order by col3):实现分组递增汇总
-
MYSQL-实现sqlserver- row_number() over(partition by order by) 分组排序功能
-
mysql 如何实现类似row_number() over partition by多个字段分组排序
-
MYSQL-实现ORACLE- row_number() over(partition by ) 分组排序_MySQL
-
Row_number() OVER(PARTITION BY xxx ORDER BY XXX)分组排序_MySQL
-
Row_number() OVER(PARTITION BY xxx ORDER BY XXX)分组排序_MySQL
-
MYSQL-实现sqlserver- row_number() over(partition by order by) 分组排序功能
-
mysql实现分组聚合(ROW_NUMBER() OVER( [PARTITION BY column_1, column_2,…] [ORDER BY column_3,colum)
-
【pandas】[4] DataFrame实现sql中row_number() over(partition by column_1 order by column_2)
-
mysql 如何实现类似row_number() over partition by多个字段分组排序