mysql bug修复时间_记录一次BUG修复数据MySQL数据

工作难，难在策划说这个以前没有旧数据的，QA说这个测过了没问题，然后上上去有旧数据，然后要我来修复旧数据。。没办法，工作本份。。修复一下，这次也是让我的sql能力提升了不少吧，从一个小菜鸡慢慢成长。。。。

开始的话时候思路不清晰，然后搞了好几个视图来做。

DROP VIEW IF EXISTS user_level;

DROP VIEW IF EXISTS user_all_user;

DROP VIEW IF EXISTS user_all_user1;

CREATE VIEW user_all_user1 AS SELECT userid,SUM(amount) AS sum FROM xg_user_log_item WHERE itemtype=95002150 AND xg_user_log_item.way=129 GROUP BY xg_user_log_item.userid;

CREATE VIEW user_all_user AS SELECT userid FROM user_all_user1 WHERE sum>=25;

UPDATE xg_user_tempdata SET data4=data4&(~(1<

UPDATE xg_user_animalsoul SET pos=0 WHERE userid NOT IN (SELECT userid FROM user_all_user) AND pos=8;

DROP VIEW IF EXISTS user_all_user;

CREATE VIEW user_all_user AS SELECT userid FROM user_all_user1 WHERE sum>=16;

UPDATE xg_user_tempdata SET data4=data4&(~(1<

UPDATE xg_user_animalsoul SET pos=0 WHERE userid NOT IN (SELECT userid FROM user_all_user) AND pos=7;

CREATE VIEW user_level AS SELECT userid,MAX(level) AS maxlevel FROM xg_user_animalsoul WHERE pos>0 GROUP BY userid;

UPDATE xg_user_tempdata JOIN user_level ON user_level.userid=xg_user_tempdata.userid SET xg_user_tempdata.data2=maxlevel WHERE xg_user_tempdata.type=20104;

DROP VIEW IF EXISTS user_level;

DROP VIEW IF EXISTS user_all_user;

DROP VIEW IF EXISTS user_all_user1;

后来用了临时表就简洁多了。

UPDATE xg_user_tempdata SET data4=data4&(~(1<<6)) WHERE type=20104 AND data4&(1<<6)>0 AND userid NOT IN

(SELECT userid FROM xg_user_log_item WHERE itemtype=95002150 AND way=129 AND amount=7);

UPDATE xg_user_tempdata SET data4=data4&(~(1<<7)) WHERE type=20104 AND data4&(1<<7)>0 AND userid NOT IN

(SELECT userid FROM xg_user_log_item WHERE itemtype=95002150 AND way=129 AND amount=9);

UPDATE xg_user_animalsoul SET pos=0 WHERE pos=7 AND userid NOT IN

(SELECT xg_user_tempdata.userid FROM xg_user_tempdata WHERE type = 20104 AND data4&(1<<6)>0);

UPDATE xg_user_animalsoul SET pos=0 WHERE pos=8 AND userid NOT IN

(SELECT xg_user_tempdata.userid FROM xg_user_tempdata WHERE type = 20104 AND data4&(1<<7)>0);

UPDATE xg_user_tempdata,(SELECT userid,MAX(level) AS maxlevel FROM xg_user_animalsoul WHERE pos>0 GROUP BY userid) AS A

SET xg_user_tempdata.data2=A.maxlevel WHERE xg_user_tempdata.type=20104 AND A.userid=xg_user_tempdata.userid;

上面的最后一条语句用了MAX和GROUP BY，后来想用ORDER BY和单独写一个，学一下写法：

UPDATE xg_user_tempdata,(SELECT DISTINCT(userid),level AS maxlevel FROM xg_user_animalsoul WHERE pos>0 ORDER BY level DESC) AS A

SET xg_user_tempdata.data2=A.maxlevel WHERE xg_user_tempdata.type=20104 AND A.userid=xg_user_tempdata.userid;

下面是多表统计数量然后去另外一个表选出对应数据，第一种方法还是视图，第二种是联表SELECT：

DROP VIEW IF EXISTS user_times;

CREATE VIEW user_times AS SELECT userid, COUNT(userid) AS times FROM xg_user_log_item WHERE way=554 AND itemtype=95002153 AND isaward=1 AND amount=40 GROUP BY userid;

SELECT id,name,server,plat,times FROM xg_user JOIN user_times ON user_times.userid=xg_user.id;

DROP VIEW IF EXISTS user_times;

SELECT id,name,server,plat,times FROM xg_user,(SELECT userid, COUNT(userid) AS times FROM xg_user_log_item WHERE way=554 AND itemtype=95002153 AND isaward=1 AND amount=40 GROUP BY userid) AS user_times WHERE id=userid;