Question

审议本表()。 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

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

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

Answer 1

这是经常出现的<条码>试验组/代码”问题。我解决该问题的通常方式,在逻辑上相当于@Martin Smith的答复,但并不使用分顺序:

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;

如果一个以上的产品具有相同的类型,而且这两种产品的价格都相等,那么,我的解决办法和迄今为止在座的所有其他产品都有机会生产出每值的的>的多行。有办法解决这一问题并打破这种鸿沟,但你需要告诉我们,如果是这样的话,哪一种产品是“双赢”。

你们需要一些其他的属性,保证这些属性在所有行中都是独一无二的,至少对于有相同的<条码>的行文而言。例如,如果产品有更大的<代码> Id 价值应当赢得,你可以解决这种方式的关联:

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;

Answer 2

<><>Edit>/strong> 为满足已查明的要求而更新地雷

SELECT Id, name, type,price 
FROM Table T1
WHERE NOT EXISTS(
          SELECT * FROM TABLE T2 
          WHERE T1.type=t2.type 
          AND T2.Price >= T1.Price 
          AND T2.Id > T1.Id
          )

Answer 3

你们可以用一种偏袒来做到这一点。

SELECT id, name, type, price FROM products p1
WHERE EXISTS (Select type, max(price) FROM Products p2 
              GROUP BY type
              WHERE p1.type=p2.type AND p1.price=p2.MAX(price))

或加入

SELECT id, name, type, price FROM products p1
INNER JOIN (Select type, max(price) FROM Products p2 GROUP BY type) maxPrice
         ON maxPrice=price=p1.price AND maxPrice.type=p1.price

友情链接