Mysql:if else从一列到另一列

Question

我想知道是否有机会从另一个表插入到一个表中 if value = '0' then import = 'no' else import = 'yes'

现在在插入时自动打开"is"。

我的sql查询:

insert into table_3 (name, data, type, value, import)
select name_batch, data_of_insert, 'File 2', SUM(value), 'yes'
from table_2
WHERE data_of_insert > DATE_SUB(now(), INTERVAL 1 SECOND)
group by name_batch");

Answer 1

您可以使用CASE:

insert into table_3 (name, data, type, value, import)
select name_batch, data_of_insert, 'File 2', SUM(value), 
    case when SUM(value) = 0 then 'no' else 'yes' end
from table_2
where data_of_insert > DATE_SUB(now(), INTERVAL 1 SECOND)
group by name_batch

仅当禁用 ONLY_FULL_GROUP_BY 时，上述内容才有效。

如果没有，请使用:

insert into table_3 (name, data, type, value, import)
select name_batch, data_of_insert, 'File 2', SUM(value), 
    case when SUM(value) = 0 then 'no' else 'yes' end
from table_2
where data_of_insert > DATE_SUB(now(), INTERVAL 1 SECOND)
group by name_batch, data_of_insert