MySQL 关于最大值的内部连接表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15402025/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Inner Join table with respect to a maximum value
提问by vinsanity38
I'm trying to write a MySQL query where I pull a seller's info and her most popular product. This is determined by the product with the most page views, i.e. MAX(page_views).
我正在尝试编写一个 MySQL 查询，在其中提取卖家的信息和她最受欢迎的产品。这是由页面浏览量最多的产品决定的，即MAX(page_views)。
The query below though is just pulling a random product and not the one with the most page views.
不过，下面的查询只是随机抽取一个产品，而不是页面浏览量最多的产品。
"SELECT
seller.id, seller.language, seller.shop_name,seller.story,
seller.eng_story, product.id, product.image_thumb, product.title,
product.eng_title, product.price, MAX(product.page_views)
FROM seller
INNER JOIN product ON seller.id=product.seller_id
WHERE seller.handpicked='y' AND seller.shop_active='y'
GROUP BY seller.id
ORDER BY product.page_views
LIMIT 0,5"
Or better said, page_viewsis in fact the correct number, but how do I get the other product fields(id, image, title, etc.) to be with respect to the product with the most page views.
或者更好地说，page_views实际上是正确的数字，但是我如何让其他产品字段（id、图像、标题等）与具有最多页面浏览量的产品相关。
TABLE DATA:
表格数据：
SELLER:
卖方：
id | language | shop_Name | story | eng_story | handpicked | active
1 | 1 | mitienda | hola mundo| Hello world | Y | Y
2 | 1 | sisenor | bonita | beautiful | N | Y
3 | 2 | new_world | mi vida | my life | Y | Y
PRODUCTS:
产品：
id | seller_id | image_thumb | title | eng_title | price | page Views
1 | 1 | /images/.. | sombrero | hat | | 10
2 | 1 | /images/.. | bufanda | scarf | | 30
3 | 2 | /images/.. | arte | art | | 15
4 | 3 | /images/.. | joyeria | jewlery | | 1
5 | 2 | /images/.. | canasta | basket | | 13
6 | 3 | /images/.. | ropa | clothes | | 6
Expected Result (condensed):
预期结果（浓缩）：
seller.id | shop_name | product.id | pageviews | title | price
1 | miteinda | 2 | 30 | bufanda |
3 | newworld | 6 | 6 | ropa |
The result should list out sellers information who have been handpicked and their most popular product by pageviews The sellers are ordered by pageviews with a limit of 5 sellers total.
结果应列出经过精心挑选的卖家信息及其按浏览量最受欢迎的产品卖家按浏览量排序，总共限制为 5 个卖家。
回答by John Woo
SELECT a.ID SellerID,
a.Shop_Name,
b.ID ProductID,
b.pageViews,
b.title,
b.Price
FROM seller a
INNER JOIN Products b
ON a.id = b.seller_ID
INNER JOIN
(
SELECT seller_ID, MAX(pageViews) max_view
FROM products
GROUP BY seller_ID
) c ON b.seller_ID = c.seller_ID AND
b.pageViews = c.max_View
WHERE a.handpicked = 'Y' AND a.active = 'Y'
OUTPUT
输出
╔══════════╦═══════════╦═══════════╦═══════════╦═════════╦═══════╗
║ SELLERID ║ SHOP_NAME ║ PRODUCTID ║ PAGEVIEWS ║ TITLE ║ PRICE ║
╠══════════╬═══════════╬═══════════╬═══════════╬═════════╬═══════╣
║ 1 ║ mitienda ║ 2 ║ 30 ║ bufanda ║ ║
║ 3 ║ new_world ║ 6 ║ 6 ║ ropa ║ ║
╚══════════╩═══════════╩═══════════╩═══════════╩═════════╩═══════╝
