Question

如果2个表格中的3个用户栏数超过一个固定栏,则试图添加一个记录。

I ve searched all over, found You can t putuser factors in IFs,WHEs et s. 缔造者可使用<条码>。 www.un.org/Depts/DGACM/index_french.htm 总损失为一米。在试图使用<代码>SUM之前,我前面的坏代码就是一个例子。 WHEREs, 条件是:

SELECT SUM(num1) INTO @mun1 FROM table1 WHERE user =  0 ;

SELECT SUM(num2) INTO @mun2 FROM table1 WHERE user =  0 ;

SELECT SUM(num3) INTO @mun3 FROM table2 WHERE column1 =  d  AND user =  0 ;

SET @mun4 = @mun1 - @mun2 - @mun3;

INSERT INTO table2 (user, column1, column2) VALUES ( 0 ,  d ,  100 ) WHERE @mun4 >= 100;

Answer 1

引证:

INSERT INTO table2 (user, column1, column2) 
select  0 ,  d ,  100 
from dual
where (SELECT SUM(num1 + num2) FROM table1 WHERE user =  0 ) +
      (SELECT SUM(num3) FROM table2 WHERE column1 =  d  AND user =  0 ) > 100;

这是“如果条件”问题的一般解决办法:

insert into ... select ... where condition

只有在条件确实的情况下,而且重要的是,如果情况不实,就不会再行了——也就是说,只有在情况真实,否则就没有发生。

Answer 2

这与@Bohemian的答复相同,但请添加一个<代码>。缩略语停止插入多个记录的条款,因为选定条款可退回多个记录

INSERT INTO table2 (user, column1, column2) 
SELECT       0 ,  d ,  100 
   FROM dual
   WHERE 
         (SELECT SUM(num1 - num2) FROM table1 WHERE user =  0 )
         (SELECT SUM(num3) FROM table2 WHERE column1 =  d  AND user =  0 ) > 
         100 
   LIMIT 1

友情链接