set @sqla='DELETE FROM '; set @sqla=CONCAT(@sqla,tableNames,' s1 WHERE NOT EXISTS ( SELECT id FROM ( SELECT MAX(id) AS id FROM ',tableNames,' GROUP BY strserialno, machinesno, transactiontype, operatoraccount, realmoneystatus, gathertime) temp) where temp.id = s1.id'); 就是sql语句,使用concat拼接的,
把这个SQL语句放到MYSQL中运行
一样运行不了,报的错是一样的,就是sql的问题,可是我实在看不出来哪里写错了 sql(我已经把游标改成实际的表名了): DELETE FROM serialnotracktemp2 s1 WHERE tableNames NOT EXISTS ( SELECT id FROM( SELECT MAX(id) as id FROM serialnotracktemp2 GROUP BY strserialno, machinesno, transactiontype, operatoraccount, realmoneystatus, gathertime)temp )where temp.id = s1.id;
sql:DELETE FROM serialnotracktemp2 s1 WHERE serialnotracktemp2 NOT EXISTS ( SELECT id FROM( SELECT MAX(id) as id FROM serialnotracktemp2 GROUP BY strserialno, machinesno, transactiontype, operatoraccount, realmoneystatus, gathertime)temp )where temp.id = s1.id; 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 's1 WHERE serialnotracktemp2 NOT EXISTS ( SELECT id FROM( S' at line 1
DELETE FROM serialnotracktemp2 s1 WHERE NOT EXISTS ( SELECT id FROM( SELECT MAX(id) as id FROM serialnotracktemp2 GROUP BY strserialno, machinesno, transactiontype, operatoraccount, realmoneystatus, gathertime)temp )where temp.id = s1.id;
还是不行啊,下面是把你发给我的运行的报错DELETE FROM serialnotracktemp2 s1 WHERE NOT EXISTS ( SELECT id FROM( SELECT MAX(id) as id FROM serialnotracktemp2 GROUP BY strserialno, machinesno, transactiontype, operatoraccount, realmoneystatus, gathertime)temp )where temp.id = s1.id; 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 's1 WHERE NOT EXISTS ( SELECT id FROM( SELECT MAX(id) as id FROM serialnotra' at line 1
where 后面刚才多了个表名,不小心粘进去的,之前我自己写的时候没有这个表名的,和这个没关系
DELETE s1 FROM serialnotracktemp2 s1 LEFT JOIN ( SELECT id FROM ( SELECT MAX(id) AS id FROM serialnotracktemp2 GROUP BY strserialno,machinesno,transactiontype,operatoraccount,realmoneystatus,gathertime) temp ) a ON a.id = s1.id WHERE a.id IS NULL
刚才括号写错了,改了之后现在是这样:DELETE FROM serialnotracktemp2 s1 WHERE NOT EXISTS ( SELECT id FROM( SELECT MAX(id) as id FROM serialnotracktemp2 GROUP BY strserialno, machinesno, transactiontype, operatoraccount, realmoneystatus, gathertime)temp where temp.id = s1.id); 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 's1 WHERE NOT EXISTS ( SELECT id FROM( SELECT MAX(id) as id FROM serialnotra' at line 1
set @sqla='DELETE FROM ';
set @sqla=CONCAT(@sqla,tableNames,' s1 WHERE NOT EXISTS (
SELECT id FROM (
SELECT MAX(id) AS id FROM ',tableNames,' GROUP BY strserialno,
machinesno,
transactiontype,
operatoraccount,
realmoneystatus,
gathertime) temp) where temp.id = s1.id');
就是sql语句,使用concat拼接的,
一样运行不了,报的错是一样的,就是sql的问题,可是我实在看不出来哪里写错了
sql(我已经把游标改成实际的表名了):
DELETE FROM serialnotracktemp2 s1 WHERE tableNames NOT EXISTS (
SELECT id FROM(
SELECT MAX(id) as id FROM serialnotracktemp2 GROUP BY
strserialno,
machinesno,
transactiontype,
operatoraccount,
realmoneystatus,
gathertime)temp )where temp.id = s1.id;
SELECT id FROM(
SELECT MAX(id) as id FROM serialnotracktemp2 GROUP BY
strserialno,
machinesno,
transactiontype,
operatoraccount,
realmoneystatus,
gathertime)temp )where temp.id = s1.id;
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 's1 WHERE serialnotracktemp2 NOT EXISTS (
SELECT id FROM(
S' at line 1
SELECT id FROM(
SELECT MAX(id) as id FROM serialnotracktemp2 GROUP BY
strserialno,
machinesno,
transactiontype,
operatoraccount,
realmoneystatus,
gathertime)temp )where temp.id = s1.id;
SELECT id FROM(
SELECT MAX(id) as id FROM serialnotracktemp2 GROUP BY
strserialno,
machinesno,
transactiontype,
operatoraccount,
realmoneystatus,
gathertime)temp )where temp.id = s1.id;
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 's1 WHERE NOT EXISTS (
SELECT id FROM(
SELECT MAX(id) as id FROM serialnotra' at line 1
LEFT JOIN
( SELECT id FROM (
SELECT MAX(id) AS id FROM serialnotracktemp2 GROUP BY
strserialno,machinesno,transactiontype,operatoraccount,realmoneystatus,gathertime) temp ) a
ON a.id = s1.id
WHERE a.id IS NULL
刚才括号写错了,改了之后现在是这样:DELETE FROM serialnotracktemp2 s1 WHERE NOT EXISTS (
SELECT id FROM(
SELECT MAX(id) as id FROM serialnotracktemp2 GROUP BY
strserialno,
machinesno,
transactiontype,
operatoraccount,
realmoneystatus,
gathertime)temp where temp.id = s1.id);
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 's1 WHERE NOT EXISTS (
SELECT id FROM(
SELECT MAX(id) as id FROM serialnotra' at line 1