选择首选Candy的高效SQL 2000查询 [英] Efficient SQL 2000 Query for Selecting Preferred Candy

问题描述

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

数据库： 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...

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

Note that the number of rows with a given 'Name' is unbounded.

所需查询结果：

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

（由于Chris对糖果手杖和巧克力的偏好相同，

(Since Chris has equal preference for Candy Cane and Chocolate, a consistent result is adequate).

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

Question: How do I Select Name, Candy from data where each resulting row contains a unique Name such that the Candy selected has the highest PreferenceFactor for each Name. (speedy efficient answers preferred).

表上需要哪些索引？如果Name和Candy是另一个表的整数索引（除了需要一些连接），是否会有所不同？

What indexes are required on the table? Does it make a difference if Name and Candy are integer indexes into another table (aside from requiring some joins)?

推荐答案

select c.Name, max(c.Candy) as Candy, max(c.PreferenceFactor) as PreferenceFactor
from Candy c
inner join (
    select Name, max(PreferenceFactor) as MaxPreferenceFactor
    from Candy
    group by Name
) cm on c.Name = cm.Name and c.PreferenceFactor = cm.MaxPreferenceFactor
group by c.Name
order by PreferenceFactor desc, Name

这篇关于选择首选Candy的高效SQL 2000查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！