SQL Server: withResult as ( Select SUM(F_dayvalue) asF_value,f_zz_ttbuildid,f_energyitemcode fromT_ec_energyitemdayresultwhereF_energyitemcode like '%000' andF_startday>=@ldStartDate andF_startday<=@ldEndDate andF_zz_ttbuildidinch(SelectF_buildid fromt_bd_buildbaseinfo)Group byF_zz_ttbuildid,f_energyitemcode)SelectA.f_value,a.f_zz_ttbuildid,b.f_buildname,a.f_energyitemcode, row_number () Over(Partition byA.f_energyitemcodeOrder byA.f_valuedesc) asNsort fromResult a Left JoinT_bd_buildbaseinfo b onA.f_zz_ttbuildid=b.f_buildid MySQL:CREATE Temporary TABLE IF not EXISTSResult (Select SUM(F_dayvalue) asF_value,f_zz_ttbuildid,f_energyitemcode fromT_ec_energyitemdayresultwhereF_energyitemcode like '%000' andF_startday>=V_ldstartdate andF_startday<=v_ldenddate andF_zz_ttbuildidinch(SelectF_buildid fromt_bd_buildbaseinfo)Group byf_zz_ttbuildid,f_energyitemcode);CREATE Temporary TABLE IF not EXISTSTMP01 (SelectA.f_value,a.f_zz_ttbuildid,b.f_buildname,a.f_energyitemcode fromResult a Left JoinT_bd_buildbaseinfo b onA.f_zz_ttbuildid=B.f_buildid); SelectF_value,f_zz_ttbuildid,f_buildname,f_energyitemcode,nsort from ( SelectHeyf_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) asNsort,@pdept:=heyf_tmp. F_energyitemcode from ( SelectF_value,f_zz_ttbuildid,f_buildname,f_energyitemcode fromTMP01Order byF_energyitemcodeASC, F_valuedesc) heyf_tmp, (Select @rownum:=0,@pdept:= NULL,@rank:=0) a) T;
mysql-implement Sqlserver-row_number () over (partition by order by) grouping sorting function