AS临时表的使用

基本语法

(SELECT * FROM TABLE) AS TEMP，然后在语句中可以直接使用TEMP中SELECT出来的字段。

1、SELECT中使用AS临时表

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;

2、UPDATE中使用临时表

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;

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;

3、多表联结不适用临时表

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);