实例解析用SQL Server处理物料清单规则

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

. ^  d. O3 s" r既然有两个计数（需要的和可得到的），那么我们必须对它们进行简单的比较。我一般采取原子对分子查询，所以我将每一条声明都保留在视图中（IngredientsAvailable_By_Recipe_vue 和IngredientsRequired_By_Recipe_vue）。然后，我用RecipeID字段做连接条件创建了第三个视图，增加了一个条件那就是可得到的（Available）必须等于需要的（Required）。为了使这个列表更吸引人，我把Recipes表也加到里面去了，所以我能获得食谱的名字。你可以查看Listing B。你也可以去掉这个逻辑来查看食谱，你可以将WHERE语句的条件改成Required > Available，但这样并不会简化操作。 2 w: ?. x3 P4 k/ Y9 Y* C0 ]- [5 O
ListingA:
3 Z% T* @* B' J# V( o0 a0 |
/ N" @% B1 j& o! |  E9 _$ o
" ?! P$ z- [* F; {4 i) |+ S0 f, C- p  k! R7 B& T. ], n) O

/ r# A. I: L; w! H& f7 D5 C, {
4 S1 E2 {4 H1 Q4 V/* count the ingredients required */SELECT      
3 x, k# w/ U2 j6 d/ m[Recipe Ingredients].RecipeID,       * r/ G& k- F; N6 \8 [9 b1 M% c  F8 v
Count([Recipe Ingredients].IngredientID) AS RequiredFROM       6 Y& E* T  E! v( N# Z
[Recipe Ingredients]GROUP BY      
( ^: |0 }0 l- o& U: B3 P [Recipe Ingredients].RecipeID;% C1 a1 h2 o& h" p# V% Q3 S
/* count the ingredients available */SELECT      
) |$ Y! J  c  }5 Y' [[Recipe Ingredients].RecipeID,       & v  s! q6 ?# J$ O) \) c
Count(Pantry.IngredientID) AS AvailableFROM Pantry      
/ g; K. D) O' _, }: GRIGHT JOIN [Recipe Ingredients]       & I. \% h2 V5 \
ON Pantry.IngredientID ) n) T; E3 ~6 U6 L' e: Z
= [Recipe Ingredients].IngredientIDGROUP BY       5 d6 A/ B( |  {
[Recipe Ingredients].RecipeID;
7 T) u  u7 c- m- |2 O& NListingB:
+ I& J, e+ w% G; t* r
9 r. Z: n3 @0 b) s4 B" v: X& X2 f8 ]  e

4 E- T5 L) `' X" s( y( x$ Z
. g# A: p, m7 t, ]9 V' I; r2 b5 C  d  t3 P0 Q4 r
SELECT      
% S, @! u: P% Z  |  [. e+ uIngredientsRequired_by_Recipe_qs.RecipeID,      
; Y9 u# G$ @1 o; p! N$ r; ZRecipes.RecipeNameFROM       4 A( n, R3 X; L
(IngredientsAvailable_By_Recipe_qs      
% f$ v1 J* L# F! _$ I1 yINNER JOIN IngredientsRequired_by_Recipe_qs       : p4 U) B, E4 B, J
ON IngredientsAvailable_By_Recipe_qs.RecipeID = IngredientsRequired_by_Recipe_qs.RecipeID)       * F: G4 c4 {7 Z4 u9 M! a
INNER JOIN Recipes      
9 |; n- n) o# N) nON IngredientsRequired_by_Recipe_qs.RecipeID
! @4 U8 n. u: Y5 q% Z- t5 x= Recipes.RecipeIDWHERE (([Available]>=[Required]));
* a: r1 ?1 T9 {2 ~1 B2 {按照早期的规定，我假定在食品室中出现的任意数量都表示这种原料是充足的。在RecipeIngredients表中需要一个Quantity列，在Pantry表中也需要一个Quantity列。（这样会使例子变得更复杂；例如，我买了盐，它的单位磅还是千克，食谱要求的一勺是大勺还是小勺呢？）
, `8 y2 s2 @7 h( G5 D+ b8 X- K; \如果你有Quantity这个列的话，一个新的挑战出现了：如果你想请朋友们吃午餐的话，你的菜单中用到了鳄梨酱、墨西哥沙拉和啤酒。现在的任务是比较需要的品种与食品室中现有的品种，然后再创建一个需要购买的清单。我把这问题留给感兴趣的读者去实现。
3 ?" F$ e7 ?* {" z$ A注意：你也可以先下载一个.NET executable程序，然后再创建数据库，并在数据库中创建你感兴趣的对象，包括表中的数据。你需要安装了.NET和SQL Server 2005才能执行这些代码。这个程序是一个winRAR文件，但是里面是.NET executable。代码可以通过Red Gate Software的SQL Packager来创建，但是你不需要Red Gate就可以运行。