工作难，难在策划说这个以前没有旧数据的，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<<(8-1))) WHERE userid NOT IN (SELECT userid FROM user_all_user) AND type=20104;
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<<(7-1))) WHERE userid NOT IN (SELECT userid FROM user_all_user) AND type=20104;
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;
网友评论