mysql从每个组中选择2行by

i have 2 table with this structure

我有两个这样结构的表

Products

产品

id       title
-----------------
1       sample 1
2       sample 2
3       sample 3
4       sample 4
5       sample 5
6       sample 6

gallery

画廊

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

and iwant this for lists of id eg(1,2,3)

我想用这个来表示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

 here is my query
这是我的查询


 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

here is real structure sqlfiddle link

这里是真正的结构sqlfiddle链接

2 个解决方案

#1

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)

EDIT:

编辑:

Try this SQL Fiddle Demo

尝试这个SQL小提琴演示

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

So basically this part

所以这部分

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

is used to replace group by typeid

用类型id替换组吗

#2

SELECT p.id,p.title, 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)

please try this

请试试这个

#1

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)