sql - php查询，如果特定列为零，则不插入
提问于 2018-03-09 13:17:04
我有一个将值插入到表'material_weight‘中的查询
这就是它：
$sql_material = "INSERT INTO `material_weight`
(mat_record_id, material_weight, material_name)
VALUES
((select MAX(record_id) FROM truck_records), '$material_wei_1', '$material_1'),
((select MAX(record_id) FROM truck_records), '$material_wei_2', '$material_2'),
((select MAX(record_id) FROM truck_records), '$material_wei_3', '$material_3'),
((select MAX(record_id) FROM truck_records), '$material_wei_4', '$material_4'),
((select MAX(record_id) FROM truck_records), '$material_wei_5', '$material_5'),
((select MAX(record_id) FROM truck_records), '$material_wei_6', '$material_6'),
((select MAX(record_id) FROM truck_records), '$material_wei_7', '$material_7'),
((select MAX(record_id) FROM truck_records), '$material_wei_8', '$material_8'),
((select MAX(record_id) FROM truck_records), '$material_wei_9', '$material_9'),
((select MAX(record_id) FROM truck_records), '$material_wei_10', '$material_10') ";我想要一个if语句，IF $material_wei_1或$material_wei_2 ...等于零不插入此行。
$sql_material = "INSERT INTO `material_weight`
(mat_record_id, material_weight, material_name)
VALUES
((select MAX(record_id) FROM truck_records), '$material_wei_1', '$material_1'),
if( '$material_wei_2' = 0, ((select MAX(record_id) FROM truck_records), '$material_wei_2', '$material_2') , ),
((select MAX(record_id) FROM truck_records), '$material_wei_3', '$material_3'),... IF语法有问题吗？
回答 1
Stack Overflow用户
发布于 2018-03-09 13:22:42
您将这些值作为参数传递到查询中。因此，我的第一反应是修复应用程序。
但是，您可以在查询中执行此操作。使用子查询将参数组合在一起，并在外部查询中只使用WHERE子句。
下面的查询实现了这一点，并且还过滤掉了重复的子查询，因此这应该比原始版本更快：
INSERT INTO material_weight (mat_record_id, material_weight, material_name)
SELECT mat_record_id, material_weight, material_name
FROM ((SELECT '$material_wei_1' as material_weight, '$material_1' as material_name) UNION ALL
(SELECT '$material_wei_2' as material_weight, '$material_2' as material_name) UNION ALL
. . .
) m CROSS JOIN
(SELECT MAX(record_id) as mat_record_id FROM truck_records) tr
WHERE material_weight > 0;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/49194672
复制相关文章
腾讯云开发者
