mysql 临时表 插入_mysql 临时表 创建和插入-阿里云开发者社区

mysql 2张表插入到临时表里

一个项目用到多张表数据合计查询

by 夜色紫宸風

DROP TABLE IF EXISTS tmp_table;

CREATE TEMPORARY TABLE tmp_table SELECT appid,uid FROM `sz_app_credit_log_201111`;

INSERT INTO tmp_table SELECT appid,uid FROM `sz_app_credit_log`;

SELECT * FROM tmp_table;

具体例子

DROP TABLE IF EXISTS tmp_table;

CREATE TEMPORARY TABLE tmp_table SELECT appid,uid,sum(ovalue) as s FROM `sz_app_credit_log_201111` l where l.appid='7' and l.operate='add' and l.dateline>=UNIX_TIMESTAMP('2011-11-01') and l.dateline

INSERT INTO tmp_table SELECT appid,uid,sum(ovalue) as s FROM `sz_app_credit_log` l where l.appid='7' and l.operate='add' and l.dateline>=UNIX_TIMESTAMP('2011-11-01') and l.dateline

SELECT t.appid,t.uid,m.tel,sum(t.s) as s FROM tmp_table t left join sz_ucenter_members m on t.uid=m.uid group by uid order by sum(t.s) desc limit 0,500;