用于选择首选糖果的高效 SQL 2000 查询

（我希望我能想出一个更具描述性的标题...如果您能说出我要询问的查询类型，请建议一个或编辑这篇文章）

数据库：SQL Server 2000

示例数据（假设 500,000 行）：

Name   Candy       PreferenceFactor
Jim    Chocolate   1.0
Brad   Lemon Drop   .9
Brad   Chocolate    .1
Chris  Chocolate    .5
Chris  Candy Cane   .5
499,995 more rows...
  

请注意，具有给定“名称”的行数是无限的。

期望的查询结果：

Jim    Chocolate   1.0
Brad   Lemon Drop   .9
Chris  Chocolate    .5
~250,000 more rows...
  

（由于克里斯对拐杖糖和巧克力的偏好相同，因此一致的结果就足够了）。

问题：如何从数据中选择名称、糖果，其中每个结果行都包含唯一的名称，以便所选糖果的每个名称具有最高的 PreferenceFactor。 （快速有效的答案优先）。

表上需要哪些索引？如果 Name 和 Candy 是另一个表的整数索引（除了需要一些联接之外），这会有什么不同吗？

您会发现以下查询优于给出的所有其他答案，因为它适用于单次扫描。这模拟了 MS Access 的 First 和 Last 聚合函数，这基本上就是您正在做的事情。

当然，您的 CandyPreference 表中可能会使用外键而不是名称。为了回答您的问题，实际上最好将 Candy 和 Name 作为另一个表的外键。

如果 CandyPreferences 表中还有其他列，那么拥有包含相关列的覆盖索引将产生更好的性能。使列尽可能小将增加每页的行数并再次提高性能。如果您最常使用 WHERE 条件执行查询来限制行，那么覆盖 WHERE 条件的索引就变得很重要。

Peter 的做法是正确的，但存在一些不必要的复杂性。

CREATE TABLE #CandyPreference (
   [Name] varchar(20),
   Candy varchar(30),
   PreferenceFactor decimal(11, 10)
)
INSERT #CandyPreference VALUES ('Jim', 'Chocolate', 1.0)
INSERT #CandyPreference VALUES ('Brad', 'Lemon Drop', .9)
INSERT #CandyPreference VALUES ('Brad', 'Chocolate', .1)
INSERT #CandyPreference VALUES ('Chris', 'Chocolate', .5)
INSERT #CandyPreference VALUES ('Chris', 'Candy Cane', .5)

SELECT
   [Name],
   Candy = Substring(PackedData, 13, 30),
   PreferenceFactor = Convert(decimal(11,10), Left(PackedData, 12))
FROM (
   SELECT
      [Name],
      PackedData = Max(Convert(char(12), PreferenceFactor) + Candy)
   FROM CandyPreference
   GROUP BY [Name]
) X

DROP TABLE #CandyPreference

实际上我不推荐这种方法，除非性能至关重要。 “规范”的方法是 OrbMan 的标准 Max/GROUP BY 派生表，然后连接到它以获取选定的行。但是，当有多个列参与 Max 的选择，并且选择器的最终组合可以重复时，即当没有列提供任意唯一性时（如这里的情况），该方法开始变得困难如果 PreferenceFactor 相同，我们就使用该名称。

编辑：最好提供更多使用说明，以帮助提高清晰度并帮助人们避免问题。

• 作为一般经验法则，当尝试提高查询性能时，如果可以节省 I/O，您可以进行大量额外的数学计算。保存整个表查找或扫描可以大大加快查询速度，即使使用所有转换和子字符串等也是如此。
• 由于精度和排序问题，使用此方法使用浮点数据类型可能不是一个好主意。不过，除非您处理的是非常大或非常小的数字，否则无论如何您都不应该在数据库中使用浮点型。
• 最好的数据类型是那些在转换为二进制或字符后不打包并按相同顺序排序的数据类型。 Datetime、smalldatetime、bigint、int、smallint 和tinyint 都直接转换为二进制并正确排序，因为它们没有打包。对于二进制，避免 left() 和 right()，使用 substring() 使值可靠地返回到原始值。
• 我利用了在此查询中小数点前面只有一位数字的 Preference，允许直接转换为 char，因为小数点之前总是至少有一个 0。如果可能有更多数字，则必须对转换后的数字进行小数对齐，以便正确排序。最简单的方法可能是乘以您的偏好评级，这样就没有小数部分，转换为 bigint，然后转换为二进制（8）。一般来说，数字之间的转换比 char 和其他数据类型之间的转换要快，尤其是对于日期数学。
• 留意空值。如果有的话，你必须将它们转换成某种东西然后再转换回来。