mysql - SQL，按最大列数选择分组

Question

我们有以下 SQL。按product_id 和计数分组。限制 6。下面的查询和示例输出。

现在，假设我想要 Group by product_id and count. Limit 6.基于表中最后 1000 条记录？所以不是整个表，而是仅基于最后 1000 条记录的分组/计数。这可以吗？

我已经使用 LIMIT 来捕获前 6 个。但是有没有办法将计数的列数限制为最大值？

希望这是清楚的

谢谢，肖恩

SELECT `t`.`product_id` AS `id`, COUNT(*) AS `cnt` 
FROM `sofreport_viewed_product_index` AS `t` WHERE (t.product_id != '24') 
AND (t.store_id = '1') GROUP BY `t`.`product_id` ORDER BY `cnt` DESC LIMIT 6

<小时/>

id  cnt
9   239
440 179
216 169
10  157
494 126
500 118

Answer 1

我认为它会像这样工作，

SELECT `t`.`product_id` AS `id`, COUNT(*) AS `cnt` FROM
`sofreport_viewed_product_index` AS `t` WHERE (t.product_id != '24')
 AND (t.store_id = '1') and t.product_id in
(SELECT id FROM sofreport_viewed_product_index ORDER BY ‘cnt‘ LIMIT 1000 )
GROUP BY `t`.`product_id` ORDER BY `cnt` DESC LIMIT 6