引言：

问题：当你连接SQL Server的时候你得到了什么，物料清单和烹饪？

回答：一个有用的技巧就是，它可以根据你手边现有的原料计算出你能做哪个食谱上的东西

大部分的数据库管理员都对典型的物料清单（BOM）问题非常熟悉：给你一些x、y和z，你能建立多少单位？SQL Server技术在BOM问题上有一个变化；它能告诉你根据手边现有的原料开出什么样的食谱。这个技术广泛地应用于各种应用软件上，所有的这些都是用BOM规则描述的。

现在你假定这样的场景：想象一下，一个SQL Server数据库是由下面这些表组成的：

• Pantry: a table that represents the sum of what's in your pantry and refrigerator.
• 食谱(Recipes)：带有描述的一系列食谱，加上各种区别加香料的、素食的等等的标记。
• 原料(Ingredients)：被系统验证过的一系列原料。
• 食谱原料(RecipeIngredients)：一张将食谱和原料连接起来的表，例如，一个食谱所需要的每一个原料都作为表中的一行。
• 食品室（Pantry）：这张表中存放着食品室和冰箱中所有物品的总数。

假如，在食品储藏室与数据库之间经常有很多异想不到的连接，当你购买原料之后需要以某种方法来更新Pantry表，这时候既需要增加数量也需要增加新的行。（在RFID时代中，这个以后也许是可能的；但是至少在现在，你一直需要自己更新表。）

现在的问题是：通过对食谱的辨认，你可以知道哪些在食品室中。解决这个问题需要下面几项：

• 由n个食谱原料组成的食谱。
• 由p个原料组成的食品室。
• 包括一些能在食品室找到原料的食谱。

你必须把食品室中的原料与食谱上的原料进行比较，如果缺少某一个原料的话应该拒绝这个食谱。（为了简单起见，如果任一种必需的原料至少有一个计量单位, 那么就可以认为这个数量是充足的了。）

你可以通过执行两个计数来解决这个问题。第一个计数简单的计算每一个食谱需要的原料；第二个计数先将RecipeIngredients表与Ingredients表连接起来，再计数。从食品室中取出任何所需要的原料后都要降低这个计数，再通过比较这个计数，你就能拒绝那些你不能得到所有原料的食谱，也可以接受符合条件的食谱。我在Listing A 中提供了一个例子。（Listing A中的数据库比我们现在讨论的复杂一点点，它有一些查询表和一些没有提到的列。）

既然有两个计数（需要的和可得到的），那么我们必须对它们进行简单的比较。我一般采取原子对分子查询，所以我将每一条声明都保留在视图中（IngredientsAvailable_By_Recipe_vue 和IngredientsRequired_By_Recipe_vue）。然后，我用RecipeID字段做连接条件创建了第三个视图，增加了一个条件那就是可得到的（Available）必须等于需要的（Required）。为了使这个列表更吸引人，我把Recipes表也加到里面去了，所以我能获得食谱的名字。你可以查看Listing B。你也可以去掉这个逻辑来查看食谱，你可以将WHERE语句的条件改成Required > Available，但这样并不会简化操作。

按照早期的规定，我假定在食品室中出现的任意数量都表示这种原料是充足的。在RecipeIngredients表中需要一个Quantity列，在Pantry表中也需要一个Quantity列。（这样会使例子变得更复杂；例如，我买了盐，它的单位磅还是千克，食谱要求的一勺是大勺还是小勺呢？）

如果你有Quantity这个列的话，一个新的挑战出现了：如果你想请朋友们吃午餐的话，你的菜单中用到了鳄梨酱、墨西哥沙拉和啤酒。现在的任务是比较需要的品种与食品室中现有的品种，然后再创建一个需要购买的清单。我把这问题留给感兴趣的读者去实现。

注意：你也可以先下载一个.NET executable程序，然后再创建数据库，并在数据库中创建你感兴趣的对象，包括表中的数据。你需要安装了.NET和SQL Server 2005才能执行这些代码。这个程序是一个winRAR文件，但是里面是.NET executable。代码可以通过Red Gate Software的SQL Packager来创建，但是你不需要Red Gate就可以运行。

责任编辑：德东

查看本文国际来源

/* count the ingredients required */
SELECT 
      [Recipe Ingredients].RecipeID, 
      Count([Recipe Ingredients].IngredientID) AS Required
FROM 
      [Recipe Ingredients]
GROUP BY 
      [Recipe Ingredients].RecipeID;


/* count the ingredients available */
SELECT 
      [Recipe Ingredients].RecipeID, 
      Count(Pantry.IngredientID) AS Available
FROM Pantry 
      RIGHT JOIN [Recipe Ingredients] 
      ON Pantry.IngredientID = [Recipe Ingredients].IngredientID
GROUP BY 
      [Recipe Ingredients].RecipeID;

SELECT 
      IngredientsRequired_by_Recipe_qs.RecipeID, 
      Recipes.RecipeName
FROM 
      (IngredientsAvailable_By_Recipe_qs 
      INNER JOIN IngredientsRequired_by_Recipe_qs 
      ON IngredientsAvailable_By_Recipe_qs.RecipeID = IngredientsRequired_by_Recipe_qs.RecipeID) 
      INNER JOIN Recipes 
      ON IngredientsRequired_by_Recipe_qs.RecipeID = Recipes.RecipeID
WHERE (([Available]>=[Required]));