mysql 如何选择2行_mysql从每个组中选择2行

我有2个这种结构的表

制品

id title

-----------------

1 sample 1

2 sample 2

3 sample 3

4 sample 4

5 sample 5

6 sample 6

画廊

id typeid name

-------------------------------

1 1 sample for 1

2 1 sample for 1

3 1 sample for 1

4 2 sample for 2

5 2 sample for 2

7 2 sample for 2

8 3 sample for 3

9 3 sample for 3

10 3 sample for 3

11 4 sample for 4

12 4 sample for 4

13 5 sample for 5

14 5 sample for 5

并且我想知道id的列表,例如(1,2,3)

id typeid name

---------------------

1 1 sample for 1

1 2 sample for 1

2 4 sample for 2

2 5 sample for 2

3 8 sample for 3

3 9 sample for 3

这是我的查询

select p.*,g.* from products p inner join gallery g ON p.id=g.typeid where p.id in (3,4,5) group by typeid

解决方法:

SELECT p.id, g.typeid, g.id, g.title

FROM products p

INNER JOIN (SELECT * FROM gallery a

WHERE (SELECT COUNT(*) FROM gallery b WHERE b.title = a.title AND b.id >= a.id) <= 2

) g ON p.id = g.typeid

WHERE p.id in (3,4,5)

编辑：

SELECT p.id, g.typeid, g.id, g.name

FROM products p

INNER JOIN (SELECT * FROM gallery a

WHERE (SELECT COUNT(*) FROM gallery b WHERE b.typeid = a.typeid AND b.id >= a.id) <= 2

) g ON p.id = g.typeid

WHERE p.id in (3,4,5) order by g.id asc

基本上这部分

WHERE (SELECT COUNT(*) FROM gallery b WHERE b.title = a.title AND b.id >= a.id) <= 2

用于通过typeid替换group

标签：sql,mysql,group-by,greatest-n-per-group

来源： https://codeday.me/bug/20190824/1710220.html