MySQL GROUP BY 中的 ORDER BY 和 LIMIT

ORDER BY and LIMIT in GROUP BY

提问by Rob

I'm trying to get a subset of records in a GROUP BY, I've seen a lot of crazy solutions out there, but they just seem too complicated, is there any more efficient way to do this.

我试图在 GROUP BY 中获取记录的子集，我已经看到了很多疯狂的解决方案，但它们看起来太复杂了，有没有更有效的方法来做到这一点。

SELECT user_id, GROUP_CONCAT(item_id ORDER BY `timestamp`) AS items 
FROM wb_user_book_current_item GROUP BY user_id

So this will return me all the current items for all users which is okay so far. But I only want the ten most recent items. Adding ORDER BYto the GROUP_CONCAThelps, but it still doesn't give me the last ten records.

所以这将返回所有用户的所有当前项目，到目前为止还可以。但我只想要最近的十个项目。添加ORDER BY到GROUP_CONCAT帮助中，但它仍然没有给我最后十条记录。

EDIT

编辑

If I do something like this and hard code the user_idthen I can get the results I want for that one user, problem is combining it so that I don't need to hard code the user_idand can for instance just get ALLusers last ten items

如果我做这样的事情并硬编码，user_id那么我可以获得我想要的那个用户的结果，问题是将它组合起来，这样我就不需要硬编码，user_id并且可以例如只获取所有用户的最后十个项目

SELECT GROUP_CONCAT(cp2.item_id) AS items 
FROM (SELECT cp.user_id, cp.item_id 
      FROM wb_user_book_current_item cp 
      WHERE cp.user_id=1 ORDER BY cp.`timestamp` 
      LIMIT 10) AS cp2 
GROUP BY cp2.user_id

采纳答案by Rob

So came across a nice solution here that works pretty well.

所以在这里遇到了一个很好的解决方案，效果很好。

http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/

http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/

It's something like this put all together:

放在一起是这样的：

SET @num := 0, @user_id := '';

SELECT cp2.user_id, CONCAT(cp2.item_id) AS items
FROM (
   SELECT cp.user_id, cp.item_id,
   @num := IF(@user_id = cp.user_id, @num + 1, 1) AS row_number,
   @user_id := cp.user_id AS dummy
   FROM wb_user_curent_item AS cp
   ORDER BY cp.user_id ASC, cp.`timestamp` DESC
) AS cp2 WHERE cp2.row_number <= 10
GROUP BY cp2.user_id

So basically it just uses the numincrement to limit the records rather than using LIMIT

所以基本上它只是使用num增量来限制记录而不是使用LIMIT

回答by lc.

This is a difficult problem, but how about this:

这是一个困难的问题，但如何解决这个问题：

SELECT user_id, GROUP_CONCAT(item_id ORDER BY `timestamp`) AS items
FROM wb_user_book_current_item T
WHERE NOT EXISTS
(
    SELECT 1
    FROM wb_user_book_current_item T2
    WHERE T2.user_id = T.user_id
    ORDER BY T2.`timestamp` DESC
    LIMIT 10,1
) 
OR T.`timestamp` > (
    SELECT T2.`timestamp`
    FROM wb_user_book_current_item T2
    WHERE T2.user_id = T.user_id
    ORDER BY T2.`timestamp` DESC
    LIMIT 10,1
)
GROUP BY user_id

This of course assumes you won't have two rows with the same timestampfor the same user.

这当然假设您不会有两行timestamp相同的用户。

If your timestamp field is always a positive integer, you can also replace the NOT EXISTS...ORwith a COALESCE:

如果您的时间戳字段始终是正整数，您还可以将 替换NOT EXISTS...OR为COALESCE：

SELECT user_id, GROUP_CONCAT(item_id ORDER BY `timestamp`) AS items
FROM wb_user_book_current_item T
WHERE T.`timestamp` > COALESCE((
    SELECT T2.`timestamp`
    FROM wb_user_book_current_item T2
    WHERE T2.user_id = T.user_id
    ORDER BY T2.`timestamp` DESC
    LIMIT 10,1
), 0)
GROUP BY user_id

---

---

Original answer, but apparently MySQL doesn't understand how to do this properly and complains the subselect returns multiple rows. Of course we want multiple rows; it's a GROUP_CONCAT. Grr.

原始答案，但显然 MySQL 不明白如何正确执行此操作并抱怨子选择返回多行。当然我们想要多行；这是一个GROUP_CONCAT. 咕噜噜

Unfortunately, I think there's no real way around using a subquery:

不幸的是，我认为使用子查询没有真正的方法：

SELECT T.user_id, 
    GROUP_CONCAT((SELECT T2.item_id 
                  FROM wb_user_book_current_item T2 
                  WHERE T2.user_id = T.user_id 
                  ORDER BY T2.`timestamp` 
                  LIMIT 10)) AS items 
FROM wb_user_book_current_item T
GROUP BY user_id

Otherwise, adding LIMITanywhere else will either limit the number of groups, or limit from the total recordset over the table (and not the group) - neither of which are what you are trying to achieve.

否则，添加LIMIT其他任何地方将限制组数，或者限制表（而不是组）上的总记录集 - 这两个都不是您想要实现的目标。

回答by ypercube??

SELECT 
    i.user_id,
    GROUP_CONCAT(i.item_id ORDER BY i.timestamp) AS items 
FROM 
    ( SELECT DISTINCT user_id
      FROM wb_user_book_current_item 
    ) AS du
  JOIN
    wb_user_book_current_item AS i
      ON  i.user_id = du.user_id
      AND i.timestamp <= COALESCE(
          ( SELECT i2.item_id 
            FROM wb_user_book_current_item AS i2
            WHERE i2.user_id = du.user_id
            ORDER BY i2.timestamp ASC 
              LIMIT 1 OFFSET 9
          )
          , '2038-01-19 03:14:07')
GROUP BY
    i.user_id ;

An index on (user_id, timestamp, item_id)will help efficiency.

索引(user_id, timestamp, item_id)将有助于提高效率。

回答by Mohamed Nuur

Try this:

尝试这个：

SELECT 
  user_id, 
  GROUP_CONCAT(item_id ORDER BY `timestamp`) AS items 
FROM wb_user_book_current_item 
GROUP BY user_id
LIMIT 0, 10

回答by Paul Sullivan

UPDATE: I didn't notice the GROUP_CONCAT so you will have to use sub query in conunction with LIMIT

更新：我没有注意到 GROUP_CONCAT，因此您必须将子查询与 LIMIT 结合使用

use LIMIT

使用限制

SELECT column_name(s)
FROM table_name
LIMIT number