通过“SELECT”命令选择每组的前两个记录的最佳方法是什么？

For instance I have the following table:

例如，我有下表：

id group data
1 1 aaa
2 1 aaa
3 2 aaa
4 2 aaa
5 2 aaa
6 3 aaa
7 3 aaa
8 3 aaa

What is the best way to select the first two records of each group by a "SELECT" command? If there is no good way to do so, what routine do you suggest?(in PHP)

通过“SELECT”命令选择每组的前两个记录的最佳方法是什么？如果没有好办法，你建议做什么例程？（在PHP中）

(model outcome)

（模型结果）

1 1 aaa
2 1 aaa
3 2 aaa
4 2 aaa
6 3 aaa
7 3 aaa

I knew that cross-joining by a.id >= b.id in a sub-query can be working but I am looking for a more scalable solution that can be applied on a table with millions of records. Thanks

我知道在子查询中通过a.id> = b.id进行交叉连接可以正常工作，但我正在寻找一种可扩展的解决方案，可以应用于具有数百万条记录的表。谢谢

3 个解决方案

#1

select a.*
from Tablename a
where 
(
   select count(*) 
   from Tablename as b
   where a.group = b.group and a.id >= b.id
) <= 2

SQLFiddle Demo
SQLFiddle演示

#2

I like this trick, that makes use of GROUP_CONCAT aggregate function, and FIND_IN_SET:

我喜欢这个技巧，它使用了GROUP_CONCAT聚合函数和FIND_IN_SET：

SELECT
  Tablename.*
FROM
  Tablename INNER JOIN (
    SELECT `group`, GROUP_CONCAT(id ORDER BY id) ids
    FROM Tablename
    GROUP BY `group`) grp ON
  Tablename.`group` = grp.`group` AND
  FIND_IN_SET(Tablename.id, ids)<=2
ORDER BY
  Tablename.`group`, Tablename.id

Performances can't be too good, as it can't make use of an index.

性能不能太好，因为它不能使用索引。

Or you can also use this:

或者您也可以使用此：

SELECT t1.id, t1.`group`, t1.data
from
  Tablename t1 INNER JOIN Tablename t2
  ON t1.`group` = t2.`group` AND t1.id>=t2.id
GROUP BY
  t1.id, t1.`group`, t1.data
HAVING
  COUNT(*)<=2
ORDER BY
  t1.`group`, t1.id, t1.data

#3

-1

You select, filter and order your query like normal and then

您可以像平常一样选择，过滤和订购查询

for MSSQL

对于MSSQL

SELECT TOP 2 * FROM foo;

From what I can remember Sybase, Oracle and possible a few other RDBMS's uses this syntax to.

从我记忆中的Sybase，Oracle和其他一些RDBMS可以使用这种语法。

for MySQL you do

对于你做MySQL

SELECT * FROM foo LIMIT 2;

Update:

更新：

Yes, I misread your question, sorry. Seems like a few of us did :)

是的，我误解了你的问题，抱歉。好像我们中的一些人做过:)

Then it depends on whether you RDBMS supports HAVING or not etc. You could construct a query using HAVING or using IN and a subquery in the IN clause.

然后，这取决于您是否支持HAVING等。您可以使用HAVING或使用IN和IN子句中的子查询来构造查询。

For MSSQL I think you could do something like (code not tested)

对于MSSQL，我认为你可以做类似的事情（未经过测试的代码）

SELECT id, data
    FROM (
        SELECT id, data, Rank() over (Partition BY group ORDER BY id DESC ) AS Rank
        FROM table
        ) rs WHERE Rank <= 2)

But since this depends on your RDBMS I ask you to look at similar questions and see which one works best for your case since MSSQL supports some things MySQL doesn't and the other way around.

但是，由于这取决于您的RDBMS，我请您查看类似的问题，看看哪一个最适合您的情况，因为MSSQL支持MySQL没有的东西，反过来。

Here are some examples

这里有些例子

Select top 10 records for each category

为每个类别选择前10条记录

How to select the last two records for each topic_id in MySQL

如何为MySQL中的每个topic_id选择最后两条记录

#1

select a.*
from Tablename a
where 
(
   select count(*) 
   from Tablename as b
   where a.group = b.group and a.id >= b.id
) <= 2

SQLFiddle Demo
SQLFiddle演示

#2