MySQL SELECT 查询从每组返回 1 行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14375099/
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
SELECT query return 1 row from each group
提问by Vill Raj
This is a product table and have few million of records.
这是一个产品表，有几百万条记录。
I want to list record as below:
Normally I use:
我想列出如下记录：
通常我使用：
SELECT id,
product_name,
store_id
FROM product
GROUP BY store_id
ORDER BY id.
Currently having SQL performance issue. I need SQL query to output result like this.
当前有 SQL 性能问题。我需要 SQL 查询来输出这样的结果。
回答by John Woo
There are many alternatives to solves this, one which I recommend is to have joined a subquery which separately gets the latest ID(assuming that the column is AUTO_INCREMENTed) for each store_ID.
有来解决了这个，一个是我推荐的是已经加入了一个子查询，其分别获取最新的很多替代ID（假设列AUTO_INCREMENTED）每次store_ID。
SELECT a.*
FROM tableName a
INNER JOIN
(
SELECT store_ID, MAX(ID) max_ID
FROM tableName
GROUP BY store_ID
) b ON a.store_ID = b.store_ID AND
a.ID = b.max_ID
for better performance, be sure to have an index on these columns: IDand store_id.
为了获得更好的性能，请确保在这些列上有一个索引：ID和store_id。
UPDATE 1
更新 1
if you want to have limit for every records, use this below,
如果您想对每条记录设置限制，请在下面使用它，
SELECT ID, product_Name, store_ID
FROM tableName a
WHERE
(
SELECT COUNT(*)
FROM tableName b
WHERE b.store_ID = a.store_ID AND b.ID >= a.ID
) <= 2;
回答by Anand thakkar
SELECT store_id,id,product_name FROM table_name
WHERE id IN (SELECT MAX(id) FROM table_name GROUP BY store_id)
ORDER BY id
this should work and you can Order by as per your req either by store_id or id.
这应该有效，您可以根据您的要求通过 store_id 或 id 进行订购。
回答by bonCodigo
Try this please:
请试试这个：
SELECT * FROM YOURTABLE B
JOIN (SELECT MAX(ID) MX FROM YOURTABLE GROUP BY STORE_ID) A
ON A.STORE_ID = B.STORE_ID
AND B.ID = A.MX
GROUP BY B.STORE_ID
;
