好吧，我有一个表（MYSQL）"产品"，其中一列名为"category_id"，用于存储来自另一个表"类别"的类别ID。因此，"category_id"列中有多个类别。例如下表。现在我想从每个类别中选择固定数量的产品（例如 2），例如我想从产品表的每个类别中选择 2 个最新产品。我应该使用什么 mysql 查询，知道吗？
产品：
+-------------+------+---------------------+
| category_id | name | timestamp |
+-------------+------+---------------------+
| 1 | abc | 2016-02-27 16:04:00 |
| 1 | def | 2016-02-28 16:10:00 |
| 1 | ghi | 2016-02-29 16:11:00 |
| 2 | pqr | 2016-02-27 16:04:00 |
| 2 | stu | 2016-02-27 16:05:00 |
| 3 | vwx | 2016-02-28 16:04:00 |
+-------------+------+---------------------+
预期成果：
产品：
+-------------+------+---------------------+
| category_id | name | timestamp |
+-------------+------+---------------------+
| 1 | def | 2016-02-28 16:10:00 |
| 1 | ghi | 2016-02-29 16:11:00 |
| 2 | pqr | 2016-02-27 16:04:00 |
| 2 | stu | 2016-02-27 16:05:00 |
| 3 | vwx | 2016-02-28 16:04:00 |
+-------------+------+---------------------+
它可能会帮助你...
检查 SQL 小提琴链接中的输出：http://sqlfiddle.com/#!9/3942d2/6
SELECT
category_id,
name,
tsDate
FROM
(
SELECT
category_id,
name,
tsDate,
@rn := IF(@prev = category_id, @rn + 1, 1) AS rn,
@prev := category_id
FROM Product
JOIN (SELECT @prev := NULL, @rn := 0) AS vars
ORDER BY category_id, tsDate DESC, name
) AS T1
WHERE rn <= 2
试试这个：
SELECT x.*
FROM my_table x
JOIN my_table y
ON y.category_id = x.category_id
AND y.timestamp >= x.timestamp
GROUP
BY x.category_id,x.timestamp
HAVING COUNT(*) <= 2;