给定两个 SELECT 语句，如何合并它们以获得一个结果减去另一个结果 - Given two SELECT statements, how to merge them to get one result minus the other?

Given two SELECT statements, how to merge them to get one result minus the other?

我想从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

我有这个：

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