因为mysql5.7没有窗口函数,但是实际上还需要实现该功能,这边将两个进行比较对比实现。
-- 一、窗口函数处理
select
ROW_NUMBER OVER(PARTITION BY QYMC,JCSJ,PKMC ORDER BY YZMC) as row_cnt
,QYMC,JCSJ,PKMC,JCZ,WRWPFL
from t_sjzx_wryzxjc_fs_rsj
where PKMC NOT LIKE '%进口%' ;
-- 二、若没有窗口函数,利用其它方式实现ROW_NUMBER OVER()
select
-- 注意这个计数项一定要放在变量设置之前,若出现和初始设置不一致才会出现1,否则就自增
if( @QYMC=a.QYMC and @PKMC = a.PKMC and @JCSJ=a.JCSJ , @curRank := @curRank +1 ,@curRank := 1 ) as row_cnt
,@QYMC := a.QYMC
,@JCSJ := a.JCSJ
,@PKMC := a.PKMC
,a.JCZ
,a.WRWPFL
from
-- 原“PARTITION BY QYMC,JCSJ,PKMC ORDER BY YZMC”,需要在这里将所有进行 order by 进行排序
(select * from t_sjzx_wryzxjc_fs_rsj WHERE PKMC NOT LIKE '%进口%' ORDER BY QYMC,JCSJ,PKMC,YZMC ) a,
(select @QYMC := null,@PKMC := null,@JCSJ := null,@curRank := 0) t_temp;