mysql根据select查询结果循环更改

最新推荐文章于 2024-07-21 03:13:00 发布

原创最新推荐文章于 2024-07-21 03:13:00 发布 · 2.1w 阅读

5 ·

CC 4.0 BY-SA版权

MYSQL 专栏收录该内容

6 篇文章

订阅专栏

本文通过两个示例展示了如何使用SQL游标从一个表中读取数据，并根据这些数据对另一个表进行条件判断及更新操作。第一个示例涉及用户红包数据的更新与删除，第二个示例则关注游戏活动箱的状态更新。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

从一表中查询出一列数据，根据这一列数据来从另外一表中进行判断修改等操作

BEGIN
	DECLARE done tinyint default 0;
	DECLARE uid int(11);
	DECLARE cs CURSOR FOR SELECT userId FROM tbuser_red_envelope_award_log WHERE fromActiveId="1454136362";
	DECLARE continue handler for sqlstate '02000' set done=1;
	open cs;
	while done<>1 do
    fetch cs into uid;
		SELECT giftNum into @giftNum FROM tbuser_red_envelope_award_log WHERE userId=uid AND gifttemplateId=101;
		IF EXISTS(SELECT id FROM tbuser_bag WHERE awardNum>=@giftNum AND userId=uid AND gifttemplateId=101)THEN 
			UPDATE tbuser_bag SET awardNum=awardNum-@giftNum WHERE awardNum>=@giftNum AND userId=uid AND gifttemplateId=101;
		END IF;

		IF EXISTS(SELECT id FROM tbuser_bag_exchange WHERE userId=uid AND excNum>=@giftNum-5 AND addTime>"2016-01-30 18:00:00")THEN
			UPDATE tbuser_bag_exchange SET excNum=excNum-@giftNum,excTotalScore=excTotalScore-@giftNum  WHERE userId=uid AND excNum>=@giftNum-5 AND addTime>"2016-01-30 18:00:00";
		END IF;

		DELETE FROM tbuser_bag_exchange WHERE excNum<=0 AND addTime>"2016-01-30 18:00:00";
	end while;
	close cs;	
END

例2：

BEGIN

		DECLARE $gameId int;			
		DECLARE STOP INT DEFAULT 0;
		DECLARE game_id CURSOR FOR SELECT id FROM tbgames_box_activity;
		DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET STOP=1;
		OPEN  game_id;
		FETCH game_id INTO $gameId;

		WHILE STOP <> 1 DO

				SET @today = CONCAT(date(NOW())," 00:00:01");

				IF EXISTS(SELECT id  FROM tbgames_box_activity WHERE id = $gameId AND addTime<@today AND isOpen=1 LIMIT 1) THEN

					SELECT awardPool,smallAwardBoxNum,awardAllocType,awardPoolType,awardPoolRemainder INTO @awardPool, @smallAwardBoxNum,@awardAllocType,@awardPoolType,@awardPoolRemainder 
							FROM tbgames_box_activity WHERE id = $gameId AND addTime<@today AND isOpen=1 LIMIT 1;

					IF @awardPoolType=0 THEN

						IF @awardAllocType=1 THEN 
							SET @num=FLOOR(1 + (RAND() * 3));
							UPDATE tbgames_box_activity SET awardPoolRemainder=@awardPool+@awardPoolRemainder,realBoxNum=@smallAwardBoxNum,addTime=NOW(),bigAwardBoxNum=@num WHERE id = $gameId AND addTime<@today;
						ELSE 
							UPDATE tbgames_box_activity SET awardPoolRemainder=@awardPool+@awardPoolRemainder,realBoxNum=@smallAwardBoxNum,addTime=NOW() WHERE id = $gameId AND addTime<@today;
						END IF;

					ELSE 

						IF @awardAllocType=1 THEN 
							SET @num=FLOOR(1 + (RAND() * 3));
							UPDATE tbgames_box_activity SET awardPoolRemainder=@awardPool,realBoxNum=@smallAwardBoxNum,addTime=NOW(),bigAwardBoxNum=@num WHERE id = $gameId AND addTime<@today;
						ELSE 
							UPDATE tbgames_box_activity SET awardPoolRemainder=@awardPool,realBoxNum=@smallAwardBoxNum,addTime=NOW() WHERE id = $gameId AND addTime<@today;
						END IF;

					END IF;

				END IF;	

		FETCH game_id INTO $gameId;  
		
		END WHILE; 

		CLOSE game_id;

END