最新消息:欢迎光临!

mysql查询字段中连续的段

mysql findever 1780浏览 0评论

最近有个需求,需要查询某个表中(百万级)中某个字段,按照连续的段分开(最终结果为50多万),比如:

id, card

1,100

2,101

3,104

4,105

需要得到:

beg, end, count

100, 101, 2

104, 105, 2

于是想到用存储过程,找了些资料,结果代码如下:

CREATE DEFINER = CURRENT_USER PROCEDURE `temp_switch_cards`(`a` int)
BEGIN
	DECLARE curCard VARCHAR(15) DEFAULT "0";
	DECLARE curMoney INT DEFAULT 0;
	DECLARE curLimit INT DEFAULT 1000;
	DECLARE curBegCard VARCHAR(15) DEFAULT "0";
	DECLARE curEndCard VARCHAR(15);
	DECLARE curCount INT DEFAULT 0;
	DECLARE done INT DEFAULT 0;
	DECLARE r_card VARCHAR(15);
	DECLARE r_money INT;

	DECLARE cur1 cursor for select card, money  from core_callback_cards where createdate BETWEEN "2013-01-01" and "2014-11-01" and user_mobilephone!=100 and card_type="switch";
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;   

	open cur1;/* 打开游标 */
	
	fetch cur1 into r_card, r_money; 	/* 读取数据到游标  */
	while done<>1 DO 				/* 若游标有下一条记录,循环  */
		
		if (curBegCard="0")  then 
			set curBegCard = r_card;
			set curMoney = r_money;
		else
			if ((r_card - curCard <> 1) || r_money<>curMoney) then
				insert into temp_switch_cards(begcard,endcard,money,count) values(curBegCard,curCard,curMoney,curCount);
				set curCount = 0;
				set curBegCard = "0";
			end if;
		end if;
		
		set curCard = r_card;
		set curMoney = r_money;
		set curCount = curCount +1;
		fetch cur1 into r_card, r_money;
		
	end while;

	insert into temp_switch_cards(begcard,endcard,money,count) values(curBegCard,curCard,curMoney,curCount);
	 
END;;

结果还是令人满意的,只有8秒就执行完了~

参考资料:http://blog.csdn.net/juanna_ding/article/details/5381188

转载请注明:Findever » mysql查询字段中连续的段

发表我的评论
取消评论

表情

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址