Question

急问在mySQL 删除记录时, WHERE 后是否不能加入运算式? 请问要如何删除用运算式的record ? 请高手指教~

DELETE FROM `SAMPLE_TABLE`
WHERE UNIX_TIMESTAMP(updatetime)-UNIX_TIMESTAMP(firsttime)>='3600'

Answer 1

where 后是可以加入运算式的,以前做过很多了! 你要加上括号的:UNIX_TIMESTAMP(updatetime)-UNIX_TIMESTAMP(firsttime) 从你的SQL来看它是一个整体的!

Answer 2

我用的MYsql 具体函数有一点点出入：而且你的表名建的太刺激了，都是in、out、return等关键字，我在表名上都添加了一个数字1，即 in1,out1,back1,return1,transfer1:
select
in1.id,
in1.amount -
if(in1.id in(select `inId` from out1), (select sum(out1.`amount`) from out1 where out1.inId=in1.id),0) +
if(in1.id in(select out1.`inId` from out1,back1 where out1.id=back1.outId), (select sum(back1.`amount`) from back1,out1 where out1.inId=in1.id and out1.id=back1.outId),0) -
if(in1.id in(select `inId` from transfer1),(select sum(transfer1.`amount`) from transfer1 where in1.id=transfer1.inId),0)-
if(in1.id in(select `inId` from return1), (select sum(return1.`amount`) from return1 where return1.inId=in1.id),0) as total
from in1
经过测试后结果为：
id， total
1. 490
2. 486
3. 489
希望能对你有所帮助

Answer 3

UNIX_TIMESTAMP(updatetime)-UNIX_TIMESTAMP(firsttime)外面加对括号试试看...