mysql - 加入右表限制

Question

假设我有两个要加入的表。分类:

id   name
----------
1    Cars
2    Games
3    Pencils
4    Books

和项目:

id   categoryid   itemname
---------------------------
1    1            Ford
2    1            BMW
3    1            VW
4    2            Tetris
5    2            Pong
6    3            Foobar Pencil Factory

我想要一个返回类别和最后最大 N(例如:2)项名称的查询:

category.id category.name item.id item.itemname
-------------------------------------------------
1           Cars          2       BMW
1           Cars          3       VW
2           Games         4       Tetris
2           Games         5       Pong
3           Pencils       6       Foobar Pencil Factory
4           Books         NULL    NULL

我想编写如下查询:

Select * From categories c
 Left Join (select * from items order by id desc) i
 On c.id=i=categoryid
  AND LIMIT 2 #comment: N=2 this line not supported
 Where i.categoryid = c.id
 Group By c.id

谢谢!

Answer 1

我并不是说它很有效，但它应该有效:

SELECT c.*, i.id, i.itemname
FROM categories c
LEFT JOIN
  (SELECT i.*
   FROM items i
   LEFT JOIN items i2 ON i.categoryid = i2.categoryid AND i2.id > i.id
   GROUP BY i.id
   HAVING COUNT(*) < 2         # this 2 = N
  ) i ON c.id = i.categoryid