mysql - 数据库中每个数据的类别由一个特定的 id 并且只允许为每个类别 mysql 获取 2

Question

可能重复 MySql Query: Select top 3 rows from table for each category .我不清楚那个答案，为什么我又问了一次。

这是我的数据库

+-------+----------+-------------+
|  id   |  app_Id  |   Content   |
+-------+----------+-------------+
|   1   |    19    | Hello Peer  |
+-------+----------+-------------+
|   2   |    20    | Hello Peer  |
+-------+----------+-------------+
|   3   |    19    | Hello Peer  |
+-------+----------+-------------+
|   4   |    19    | Hello Peer  |
+-------+----------+-------------+
|   5   |    21    | Hello Peer  |
+-------+----------+-------------+
|   6   |    20    | Hello Peer  |
+-------+----------+-------------+
|   7   |    19    | Hello Peer  |
+-------+----------+-------------+
|   8   |    20    | Hello Peer  |
+-------+----------+-------------+
|   9   |    21    | Hello Peer  |
+-------+----------+-------------+

现在如何通过 app_Id 选择记录类别并为每个类别限制 2 个并按 appId desc 排序

如果上面的陈述不清楚。这是我从上面的数据库中预期的输出记录。

+-------+----------+-------------+
|  id   |  app_Id  |   Content   |
+-------+----------+-------------+
|   4   |    19    | Hello Peer  |
+-------+----------+-------------+
|   7   |    19    | Hello Peer  |
+-------+----------+-------------+
|   6   |    20    | Hello Peer  |
+-------+----------+-------------+
|   8   |    20    | Hello Peer  |
+-------+----------+-------------+
|   5   |    21    | Hello Peer  |
+-------+----------+-------------+
|   9   |    21    | Hello Peer  |
+-------+----------+-------------+

我想让查询执行此操作，你知道吗？

Answer 1

试试这个:

SELECT id, app_Id, Content 
FROM (SELECT id, app_Id, Content, 
             IF(@appId=@appId:=app_Id, @rowNo:=@rowNo+1, @rowNo:=1) AS rowNo 
      FROM tableA, (SELECT @rowNo:=1, @appId:=0) a
      ORDER BY app_Id, id DESC
     ) AS a 
WHERE rowNo <= 2
ORDER BY app_Id