MySQL在每个邮政编码中找到最昂贵的 [英] MySQL finding the most expensive in each zip code

问题描述

我有一个名为Products的表，该表具有架构(名称，城市，州，邮政编码价格).

I have a table called Products with the schema (name, city, state, zip_code price).

我想找到给定州的每个邮政编码中最昂贵的产品的名称.

And I want to find the most expensive products' name for a given state's each zip_code.

我写了

SELECT zip_code, MAX(price)
FROM products
WHERE products.state = 'NJ'
GROUP BY zip_code

作为子查询，但我不知道在'NJ'中显示每个zip_code的产品名称和价格

as a subquery, but I couldn't figure out displaying product name and price per zip_code in 'NJ'

如果您能帮助我，我将不胜感激， 谢谢.

I would appreciate if you can help me, Thanks.

推荐答案

SELECT
      t.name, t.city, t.zip_code, t.price
FROM  
       ( SELECT zip_code
              , MAX(price) as price 
         FROM products 
         WHERE state = 'NJ' 
         GROUP BY zip_code
       ) AS tm 
    JOIN
        products as t
            ON  tm.zip_code = t.zip_code 
            AND tm.price = t.price
WHERE 
        t.state = 'NJ' 

这篇关于MySQL在每个邮政编码中找到最昂贵的的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！