mysql 最大连续出现次数_mysql或oracle查询连续相等最大次数

-- Table structure for temp_param_result

DROP TABLE IF EXISTS temp_param_result; CREATE TABLE temp_param_result ( id int(11) NOT NULL, paramCode varchar(255) DEFAULT NULL, resultStr varchar(255) DEFAULT NULL, createdt datetime DEFAULT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-----------------------------------------------mysql 解决方案------------------------------------------- SELECT aa.paramCode, aa.resultStr, max(rownum) maxrow FROM ( SELECT ib.paramCode, ib.resultStr, @pname, @paramCode, IF ( (ib.resultStr = @pname && ib.paramCode = @paramCode), @ROW :=@ROW + 1, @ROW := 1 ) rownum ,@pname := ib.resultStr pn ,@paramCode := ib.paramCode pc FROM (SELECT * from temp_param_result order by paramCode, id) ib, ( SELECT @ROW := 1, @pname := '', @paramCode := '' ) row1 ) aa GROUP BY aa.paramCode,aa. resultStr; -----------------------------------------------------oracle 解决方案------------------------------------------

select te.paramCode, max(case when te.resultStr = te.next_result or te.next_result='X' then te.next_rn - te.rn + 1 else te.next_rn - te.rn - 1 end) cnt from (select tx.paramCode, tx.resultStr, tx.next_result, tx.rn, lead(tx.rn, 1, tx.rn) over(partition by tx.paramCode order by tx.rn) next_rn from (select t.paramCode, t.resultStr, row_number() over(partition by t.paramCode order by t.id) rn, lag(t.resultStr, 1, 'X') over(partition by t.paramCode order by t.id) prev_result, lead(t.resultStr, 1, 'X') over(partition by t.paramCode order by t.id) next_result from temp_param_result t) tx where tx.rn='1' or (tx.prev_result <> tx.next_result) ) te group by te.paramCode