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

richardtallent 发表于 Dev

理财人才

我有两个表，再加上一个匹配表。为了论证，我们称它们为“食谱和配料”。每个食谱应至少包含一种成分，但可以有许多成分。每种成分都可以在许多食谱中使用。

 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

更新：我有一个偷偷摸摸的怀疑，如果有解决方案，它将涉及交叉连接以将配方/成分的每种组合相互比较，然后以某种方式进行过滤。

弗格斯·鲍恩

我想这会给您您想要的东西（根据您提供的小提琴）

-- 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;