如何使用SQL MAX函数获取一行的所有字段? [英] How to get all the fields of a row using the SQL MAX function?

问题描述

请查看此表(来自 http://www.tizag.com/mysqlTutorial/mysqlmax. php ):

Id     name               type     price 
123451 Park's Great Hits  Music    19.99 
123452 Silly Puddy        Toy      3.99 
123453 Playstation        Toy      89.95 
123454 Men's T-Shirt      Clothing 32.50 
123455 Blouse             Clothing 34.97 
123456 Electronica 2002   Music    3.99 
123457 Country Tunes      Music    21.55 
123458 Watermelon         Food     8.73

此SQL查询返回每种类型中最昂贵的物品: SELECT类型，MAX(价格)来自产品GROUP BY类型

This SQL query returns the most expensive item from each type: SELECT type, MAX(price) FROM products GROUP BY type

Clothing $34.97
Food     $8.73
Music    $21.55
Toy      $89.95

我还希望为每行获取属于上述最高价格的字段 id 和 name . 什么SQL查询将返回这样的表?

I also want to get the fields id and name that belong to the above max price, for each row. What SQL query will return a table like this?

Id     name            type      price
123455 Blouse          Clothing  34.97
123458 Watermelon      Food      8.73
123457 Country Tunes   Music     21.55
123453 Playstation     Toy       89.95

推荐答案

这是经常出现的greatest-n-per-group问题.我通常的解决方法在逻辑上等同于@Martin Smith给出的答案，但不使用子查询:

This is the greatest-n-per-group problem that comes up frequently. My usual way of solving it is logically equivalent to the answer given by @Martin Smith, but does not use a subquery:

SELECT T1.Id, T1.name, T1.type, T1.price 
FROM Table T1
LEFT OUTER JOIN Table T2
  ON (T1.type = T2.type AND T1.price < T2.price)
WHERE T2.price IS NULL;

我的解决方案以及到目前为止在该线程上给出的所有其他解决方案，如果一个以上的产品共享相同的类型并且两者的价格相同(即最大值)，则每个type值都有可能产生多行.有很多方法可以解决这个问题并打破平局，但是您需要告诉我们哪种产品胜出"，以防万一.

My solution and all others given on this thread so far have a chance of producing multiple rows per value of type, if more than one product shares the same type and both have an equal price that is the max. There are ways to resolve this and break the tie, but you need to tell us which product "wins" in case like that.

您还需要其他一些属性，这些属性必须保证在所有行中都是唯一的，至少对于具有相同type的行而言.例如，如果具有更大Id值的产品获胜，则可以通过以下方式解决平局:

You need some other attribute that is guaranteed to be unique over all rows, at least for rows with the same type. For example, if the product with the greater Id value should win, you can resolve the tie this way:

SELECT T1.Id, T1.name, T1.type, T1.price 
FROM Table T1
LEFT OUTER JOIN Table T2
  ON (T1.type = T2.type AND (T1.price < T2.price
       OR T1.price = T2.price AND T1.Id < T2.Id))
WHERE T2.price IS NULL;

这篇关于如何使用SQL MAX函数获取一行的所有字段?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！