【SQL】Mysql5.7版本实现row_number分组排序功能

(相关资料图)

2023-05-14964

#建表dropTABLEifEXISTStest;createtabletest(idvarchar(50),ftimevarchar(50));#插入测试数据insertintotest(id,ftime)values(130,2022-09-26),(130,2022-09-27),(130,2022-09-28),(131,2022-09-26),(131,2022-09-27),(131,2022-09-28),(132,2022-09-26),(132,2022-09-27),(132,2022-09-28),(133,2022-09-27),(133,2022-09-28),(134,2022-09-27);#查看数据select*fromtestORDERBYid,ftime;#实现row_numberselectid,ftime,@rn:=casewhen@rnid=idthen@rn+1else1endasnum,@rnid:=idfrom(selectdistinctid,ftimefromtestORDERBYid,ftimedesc)a,(select@rnid=0,@rn=0)b#清空或删除表TRUNCATEtest;dropTABLEtest;SELECTa.cus_name,a.lar_no,a.prd_id,a.prc,a.lar_no1,@rn:= case when @rnid=CONCAT(a.cus_name,a.lar_no) then @rn+1 else 1 end as num,@rnid:=CONCAT(a.cus_name,a.lar_no) FROM(select a.*,b.lar_no as lar_no1 from sc_jg_prc a,prdt b where a.prd_id=b.id and a.cus_id=b.cus_no1unionselect a.*,b.lar_no as lar_no1 from sc_jg_prc a,prdt b where a.lar_no=b.lar_no and a.cus_id=b.cus_no1 ) a, (SELECT @rnid:=0,@rn:=0) AS t  where a.cus_name="长沙星沃文化传播有限公司" and ((a.lar_no="1639471423463727104" and ifnull(a.prd_id,"")="")or (a.lar_no="1639471423463727104" and ifnull(a.prd_id,"")="1668976816071639040"))