sql - 为分组查询中的每个类别选择“最佳”行的优雅方式

Question

我想从表格中为每种项目类型选择“最佳”行（即得分最高/最低的一行）。

举一个简单的例子，假设我针对不同类型的水果流行不同颜色进行了大规模调查，并确定了一些水果颜色组合的以下得分：

  Fruit    | Color   | Score              
  ==========================
  apples   | red     | 5
  apples   | yellow  | 5
  apples   | green   | 4
  apples   | blue    | 0
  bananas  | brown   | 1
  bananas  | blue    | 0
  bananas  | yellow  | 5
  kiwis    | orange  | 1
  kiwis    | brown   | 5
  kiwis    | blue    | 0

现在，我想告诉我的产品设计团队应该为每种水果选择哪种颜色。如果两种颜色同样受欢迎，我希望按字母顺序排在第一位的颜色只是为了获得确定性的结果。

大概的正常方法是编写一个程序，查询数据库中所有水果的列表（伪代码），然后运行如下的foreach：

  result = query("SELECT DISTINCT fruit FROM fruits;")
  foreach fruit in result:
      color = query("SELECT color FROM fruits WHERE fruit='$fruit' ORDER BY score DESC, color LIMIT 1")
      print "The $fruit should be $color."

如果要在SQL中执行此操作，则可以在子查询中计算分数，然后选择与分数匹配的行或对其进行联接（使用MIN和GROUP BY消除可能的重复项）：

SELECT fruit, MIN(color) FROM
  fruits
  JOIN 
    (SELECT fruit, max(score) AS score FROM fruits GROUP BY fruit)
  USING (fruit, score)
GROUP BY fruit

您可以在 this SQL Fiddle中找到示例数据和此查询。

仅在SQL / SQLite中有解决此问题的更优雅的方法吗？

在这个简单的示例中，自行连接看起来可能不太糟糕，因为我可以使用MAX + GROUP BY技巧摆脱此处的重复项。如果我需要选择其他列，例如愿意购买该颜色水果的人数，我将不得不再次嵌套查询。这导致了这样的怪兽：

SELECT fruit, color, interested FROM
  (
    SELECT fruit, color, interested FROM
      fruits
      JOIN 
        (SELECT fruit, max(score) AS score FROM fruits GROUP BY fruit)
      USING (fruit, score)
  ) 
  JOIN
  (
    SELECT fruit, min(color) as color FROM
      fruits
      JOIN 
        (SELECT fruit, max(score) AS score FROM fruits GROUP BY fruit)
      USING (fruit, score)
    GROUP BY fruit
  )
  USING (fruit, color)

（对于怪兽，为 Fiddle）

Answer 1

将两个排序列都组合为一个值可让您使用简单的MIN。
这需要formatting得分值作为固定长度的字符串：

SELECT fruit,
       MIN(printf("%10d", score) || color)
FROM fruits
GROUP BY fruit

在SQLite 3.7.11或更高版本中，保证非聚合列来自与MIN / MAX（ changelog）匹配的同一记录：

SELECT fruit,
       score,
       color,
       interested,
       MIN(printf("%10d", score) || color)
FROM fruits
GROUP BY fruit