tsql - 根据 T-SQL 中多个相关记录的排序顺序对记录进行排名？

Question

我有两张 table ，加上一张匹配的 table 。为了论证起见，我们称它们为食谱和配料。每个食谱应至少包含一种成分，但也可能有多种成分。每种成分可用于许多食谱。

 Recipes           Ingredients       Match
 ===============   ===============   ===============
 ID   int          ID   int           RecipeID     int
 Name varchar      Name varchar       IngredientID int

样本数据:

 Recipes           Ingredients       Match (shown as CDL but stored as above)
 ===============   ===============   ===============
 Soup              Chicken           Soup:        Chicken, Tomatoes
 Pizza             Tomatoes          Pizza:       Cheese, Chicken, Tomatoes
 Chicken Sandwich  Cheese            C. Sandwich: Bread, Chicken, Tomatoes
 Turkey Sandwich   Bread             T. Sandwich: Bread, Cheese, Tomatoes, Turkey        
                   Turkey

问题是:我需要对 进行排序食谱基于他们的姓名 配料 .鉴于上述示例数据，我需要这样的食谱排序顺序:

 Turkey Sandwich    (First ingredient bread, then cheese)
 Chicken Sandwich   (First ingredient bread, then chicken)
 Pizza              (First ingredient cheese)
 Soup               (First ingredient chicken)

按第一种成分对食谱进行排名很简单:

 WITH recipesranked AS (
    SELECT Recipes.ID, Recipes.Name, Recipes.Description,
        ROW_NUMBER() OVER (ORDER BY Ingredients.Name) AS SortOrder
    FROM
        Recipes
        LEFT JOIN Match ON Match.RecipeID = Recipes.ID
        LEFT JOIN Ingredients ON Ingredients.ID = Match.IngredientID
    )
 SELECT   ID, Name, Description, MIN(SortOrder)
 FROM     recipesranked
 GROUP BY ID, Name, Description; 

除此之外，我被困住了。在我上面的例子中，这几乎有效，但让两个三明治的顺序不明确。

我有一种感觉， MIN(SortOrder)应该用其他东西代替，也许是一个相关的子查询，寻找同一 CTE 中不存在另一条记录，但还没有弄清楚细节。

有什么想法吗？

(食谱可能没有成分。我不在乎它们的顺序如何，但最终会是理想的。此时不是我主要关心的问题。)

我正在使用 SQL Server 2008 R2。

更新:我为此添加了一个 SQL Fiddle，并在此处更新了示例以匹配:

http://sqlfiddle.com/#!3/38258/2

更新:我偷偷怀疑，如果有解决方案，它涉及交叉连接，将配方/成分的每种组合与其他组合进行比较，然后以某种方式对其进行过滤。

Answer 1

我认为这会给你你想要的(基于你提供的 fiddle )

-- Show recipes ranked by all their ingredients alphabetically
 WITH recipesranked AS (
    SELECT Recipes.ID, Recipes.Name, SortedIngredients.SortOrder
    FROM
        Recipes
        LEFT JOIN Match ON Match.RecipeID = Recipes.ID
        LEFT JOIN
        (
          SELECT ID, Name, POWER(2.0, ROW_NUMBER() OVER (ORDER BY Name Desc)) As SortOrder 
          FROM Ingredients) AS SortedIngredients
         ON SortedIngredients.ID = Match.IngredientID
    )
 SELECT   ID, Name, SUM(SortOrder)
 FROM     recipesranked
 GROUP BY ID, Name
 -- Sort by sum of the ingredients. Since the first ingredient for both kinds 
 -- of sandwiches is Bread, this gives both of them the same sort order, but
 -- we need Turkey Sandwiches to come out first between them because Cheese
 -- is it's #2 sorted ingredient, but Chicken is the #2 ingredient for
 -- Chicken sandwiches.
 ORDER BY SUM(SortOrder) DESC;

它只是使用 POWER 来确保最重要的成分首先得到加权。

这适用于任意数量的食谱和多达 120 种成分(总共)

如果食谱包含重复的成分，则不起作用，但如果它们可能发生，您可以将它们过滤掉