mysql - 插入子查询条件

我有这个查询

INSERT INTO subitems_detail (groepid, filiaalid, subitemsid)
SELECT DISTINCT (SELECT id FROM prijsgroepen), 0, subitems.id FROM subitems;

如何创建一个条件，当MySQL不是prijsgroepen.id时1仅插入行？

我尝试过类似的操作，但是数据库仍然插入行并将0分配给groepid，而表应该为空：

INSERT INTO subitems_detail (groepid, filiaalid, subitemsid)
SELECT DISTINCT (SELECT id FROM prijsgroepen WHERE id != 1), 0, subitems.id FROM subitems;

最佳答案

INSERT INTO subitems_detail (groepid, filiaalid, subitemsid)
SELECT DISTINCT (SELECT id FROM prijsgroepen) As groepid, 0, subitems.id
FROM subitems
HAVING greopid <> 1
;

假设prijsgroepen中只有一行；否则我很确定查询仍然会中断。

子查询作为SELECT COUNT(1) FROM prijsgroepen WHERE id != 1可能更有用，并将HAVING调整为HAVING greopid = 0

对于每个非一个prijsgroepen.id的一组子项：

INSERT INTO subitems_detail (groepid, filiaalid, subitemsid)
SELECT DISTINCT prijsgroepen.id, 0, subitems.id
FROM prijsgroepen, subitems
WHERE prijsgroepen.id <> 1
   AND prijsgroepen.id NOT IN (SELECT groepid FROM subitems_detail)
;

AND行是可选的，以防止重复处理先前处理过的prijsgroepen.id值。