SELECT查询从每个组返回1行 [英] SELECT query return 1 row from each group

问题描述

这是一个产品表，有几百万条记录.

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.

当前存在SQL性能问题.我需要SQL查询来输出这样的结果.

Currently having SQL performance issue. I need SQL query to output result like this.

推荐答案

有许多解决方案，我建议一个方法是加入一个子查询，该子查询分别获取最新的ID(列是每个store_ID的AUTO_INCREMENT ed ).

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.

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

• SQLFiddle演示
• SQLFiddle Demo

为获得更好的性能，请确保在这些列上有一个索引:ID和store_id.

for better performance, be sure to have an index on these columns: ID and store_id.

更新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;

• SQLFiddle演示
• SQLFiddle Demo

这篇关于SELECT查询从每个组返回1行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！