mysql第二大数据查询(使用rank)

表结构

CREATE TABLE `api_bdtransfer_operationrecord` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `bdtransfer_id` int(11) NOT NULL,
  `operate_user_id` int(11) NOT NULL,
  `action` smallint(6) NOT NULL,
  `content` varchar(128) NOT NULL,
  `operate_time` datetime(6) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `bdtransfer_id_refs_id_b4986c86` (`bdtransfer_id`),
  KEY `operate_user_id_refs_id_98041963` (`operate_user_id`),
  CONSTRAINT `bdtransfer_id_refs_id_b4986c86` FOREIGN KEY (`bdtransfer_id`) REFERENCES `api_bdtransfer` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `operate_user_id_refs_id_98041963` FOREIGN KEY (`operate_user_id`) REFERENCES `auth_user` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=214098 DEFAULT CHARSET=utf8;

 

语句

SELECT s.bdtransfer_id,s.id,s.action,rank FROM
  (SELECT api_bdtransfer_operationrecord.id,api_bdtransfer_operationrecord.action,
  api_bdtransfer_operationrecord.bdtransfer_id,
  CASE
  when @prevRank < api_bdtransfer_operationrecord.id then @incRank:=1
  else @incRank := @incRank + 1
  END as nextRank,
  @curRank := IF(@prevRank = api_bdtransfer_operationrecord.id, @curRank, @incRank) AS rank,
  @prevRank := api_bdtransfer_operationrecord.id
  FROM api_bdtransfer_operationrecord, (
  SELECT @curRank :=0, @prevRank := NULL, @incRank := 0
  ) r
where api_bdtransfer_operationrecord.action=1
ORDER BY api_bdtransfer_operationrecord.bdtransfer_id,api_bdtransfer_operationrecord.id desc) s
where s.bdtransfer_id in (87705,87688)