mysql - 插入子查询条件

Question

我有这个查询

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;

Answer 1

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 值。