php - 给定两个 SELECT 语句，如何合并它们以获得一个结果减去另一个结果？

Question

我想从 wantedIDs 中删除 UNwantedIDs，如何进行子查询并得到一个结果？

这是我的 UNwantedIDs 查询:

SELECT 
    GROUP_CONCAT(DISTINCT `recipe_ingredient`.`recipeId` ORDER BY `recipe_ingredient`.`recipeId` ASC) UNwantedIDs
FROM `recipe_ingredient` 
WHERE `recipe_ingredient`.`ingredientId` 
     IN(SELECT `ingredient`.`ingredientId` AS unlinkIng 
        FROM `ingredient` 
        WHERE `ingredient`.`ingredientId` IN(1) or `ingredient`.`linkIngredientPerent` IN(1))
ORDER BY `recipeId` ASC  

http://sqlfiddle.com/#!2/84f00/5

这是我的 wantedIDs 查询:

SELECT 
    GROUP_CONCAT(DISTINCT `recipe_ingredient`.`recipeId` ORDER BY `recipe_ingredient`.`recipeId` ASC) wantedIDs
FROM `recipe_ingredient` 
WHERE `recipe_ingredient`.`ingredientId` 
     IN(SELECT `ingredient`.`ingredientId` AS unlinkIng 
        FROM `ingredient` 
        WHERE `ingredient`.`ingredientId` IN(4,178) or    `ingredient`.`linkIngredientPerent` IN(4,178))
ORDER BY `recipeId` ASC  

http://sqlfiddle.com/#!2/84f00/4

结果 ID 例如:

2,3,7,8,11,21,24,36,37,41,67,70,75,80,83,99,108 

Answer 1

我有这个:

SELECT 
    GROUP_CONCAT(
        DISTINCT `recipe_ingredient`.`recipeId`
        ORDER BY `recipe_ingredient`.`recipeId` ASC
    ) wantedIDs
FROM `recipe_ingredient` 
WHERE `recipe_ingredient`.`ingredientId` 
     IN(SELECT `ingredient`.`ingredientId` AS unlinkIng 
        FROM `ingredient` 
        WHERE
          (`ingredient`.`ingredientId` IN(4,178) OR
           `ingredient`.`linkIngredientPerent` IN(4,178)
          )
      )
      AND `recipe_ingredient`.`recipeId` NOT IN (
          SELECT 
          `recipe_ingredient`.`recipeId`
             FROM `recipe_ingredient` 
          WHERE `recipe_ingredient`.`ingredientId` 
               IN (SELECT `ingredient`.`ingredientId` AS unlinkIng 
                  FROM `ingredient` 
                  WHERE `ingredient`.`ingredientId` IN(1) OR
                       `ingredient`.`linkIngredientPerent` IN(1)
               )
      )
ORDER BY `recipeId` ASC  

这是用于合并两个查询的 NOT IN 的结果。目前速度相当慢，可能是因为您需要添加一些索引。

http://sqlfiddle.com/#!2/84f00/16