选择最大，分组依据并显示不在分组依据子句中的其他列 [英] select max, group by and display other column that's not in group by clause

问题描述

为了简短起见，我在网上找到了一个教程，并紧随其后: http://www.tizag.com/mysqlTutorial/mysqlmax.php

To keep it short, I have found a tutorial online and followed to the point: http://www.tizag.com/mysqlTutorial/mysqlmax.php

SELECT type, MAX(price) FROM products GROUP BY type

我的问题是: 如何显示最昂贵的衣服"(在本例中为上衣")?

My question is: How do I echo which "clothing" is the most expensive (In this case "Blouse")?

更新:

对不起，我不好.我需要让自己更清楚.我正在寻找一种解决方案，以显示每个名称"最昂贵的地方:

Sorry guys, my bad. I needed to make myself more clear. What I am looking for is a solution that shows each "name" where they are most expensive:

name         type          price

Clothing    Blouse         34.97

Toy       Playstation      89.95

Music     Country Tunes    21.55

推荐答案

尝试以下查询:

解决方案1:

SELECT 
    products.name,
    products.type,
    products.price 
FROM products 
INNER JOIN 
( 
    SELECT type,MAX(price) max_price
    FROM products 
    GROUP BY type  ) t
ON products.type = t.type
AND products.price = t.max_price;

此处演示

解决方案2:

SELECT
    products.name,
    products.type,
    products.price 
FROM
    products
WHERE   (type, price) IN (
        SELECT type, MAX(price) max_price
        FROM products
        GROUP BY type )

请参见演示

注意:如果两个解决方案共享相同的maximum价格，则这两种解决方案都可能会为您提供多个在同一type下的产品.

Note: Both solutions might give you the multiple products under same type if they share the same maximum price.

如果您严格希望每种类型最多个项目，则需要在最后一行再次 group by .

If you strictly want at most one item from each type then you need to group by again in the last line.

因此，对于这两种解决方案，最后一行都是:

So for both solutions the last line would be:

GROUP BY products.type, products.price

查看其演示

这篇关于选择最大，分组依据并显示不在分组依据子句中的其他列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！