mysql选择每组前n行 [英] mysql select the first n rows per group
问题描述
我读过这篇文章: http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/ 并搜索其他问题
I've read this article: http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/ and search for other questions
我有一个类似于以下内容的表:
I have a table that is something like:
| table.id | USER.id
----------------------------------------------
| 1 | 101
| 2 | 101
| 3 | 101
| 4 | 101
| 5 | 101
| 6 | 101
| 7 | 101
| 8 | 101
| 9 | 101
| 10 | 101
| 11 | 102
| 12 | 102
| 13 | 102
| 14 | 102
| 15 | 103
| 16 | 103
| 17 | 103
| 18 | 103
| 19 | 103
| 20 | 103
| 21 | 103
| 22 | 103
| 23 | 103
| 24 | 104
| 25 | 104
| 26 | 104
| 27 | 104
| 28 | 104
| 29 | 104
| 30 | 105
| 31 | 105
| 32 | 105
| 33 | 106
| 34 | 106
我试图获取由user.id分组的table.id数量,如果user.id的计数大于7,只显示结果为7(也就是将计数结果限制为7)。
I'm trying to get the count of table.id grouped by user.id, and if the count of user.id is more than 7, only display the result as 7 (aka limiting the count results to 7).
在这个例子中,结果应该是:
In this example, the result should be:
| USER.id | count of table.ID
----------------------------------------
| 101 | 7
| 102 | 4
| 103 | 7
| 104 | 6
| 105 | 3
| 106 | 2
我试过了:
I've tried:
SELECT USERid, COUNT(table.id)
FROM table
WHERE table.id IN (select top 7 table.id from table)
GROUP BY USERid
和
SELECT USERid, COUNT(table.id)
FROM table
WHERE (
SELECT COUNT(table.ID) FROM table as t
WHERE t.id = t.id AND t.USERid <= table.USERid
) <= 7
GROUP BY USERid
推荐答案
您可以简化查询,并使用 LEAST 函数
You can simplify your query, and use LEAST function
SELECT USERid, LEAST(7, COUNT(*))
FROM table
GROUP BY USERid
您的评论
from the question in your comment
SELECT SUM(countByUser)
FROM
(SELECT LEAST(7, COUNT(*)) as countByUser
FROM table
GROUP BY USERid) c
这篇关于mysql选择每组前n行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!