用Create语句删除mysql中的表

Question

我正面临这个问题，我试图删除表，如果它存在，但我一直得到错误。这是我的

DROP TABLE IF EXISTS clips_cur;
CREATE TEMPORARY TABLE clips_cur(
SELECT year_r, month_n_r, IFNULL(SUM(current_clip_count),0) as curclips
FROM data_mining.clip_summary stb 
JOIN mstr_all_offer_sum o 
JOIN mstr_all_clip_red_sum d
WHERE stb.offer_id=o.oid AND
d.offer_id = o.oid AND
clip_date>='2022-03-01'AND 
clip_date>=SUBDATE( o.st_d, INTERVAL 1 DAY) AND 
clip_date<=ADDDATE(o.end_d, INTERVAL 1 DAY) AND 
clip_date<='2022-03-28' AND
is_handraiser<>1 
group by year_r, month_n_r)

错误如下:

SQL Error[1064][42000]:你的SQL语法有错误;检查该手册对应您的MySQL服务器版本为正确clips_cur(SELECT year_r，

月子_n_r, IFNULL(SUM(current_' at line 2)错误位置:line: 1

感谢你的帮助!

Answer 1

使用"AS"不是"()";试试这个:

DROP TABLE IF EXISTS clips_cur;
CREATE TEMPORARY TABLE clips_cur
AS
SELECT year_r, month_n_r, IFNULL(SUM(current_clip_count),0) as curclips
FROM data_mining.clip_summary stb 
JOIN mstr_all_offer_sum o 
JOIN mstr_all_clip_red_sum d
WHERE stb.offer_id=o.oid AND
d.offer_id = o.oid AND
clip_date>='2022-03-01'AND 
clip_date>=SUBDATE( o.st_d, INTERVAL 1 DAY) AND 
clip_date<=ADDDATE(o.end_d, INTERVAL 1 DAY) AND 
clip_date<='2022-03-28' AND
is_handraiser<>1 
group by year_r, month_n_r

Answer 2

我试过了，工作给我，你有错误与JOIN其不完整您需要使用"ON"不是"WHERE">

的例子:

Select t1.field1,t2.field1 
From Table1 t1
Join Table2 t2 on t1.fkfield = t2.keyfield
Where t1.id = 1