mysql从每个组中选择2行 [英] mysql select 2 row from each group by

问题描述

我有这张结构的两张表

产品
$ b

 
  id title 
 ----------------- 
 1样本1 
 2样本2 
 3样本3 
 4样本4 
 5样本5 
 6样本6  
 

gallery

 
  id typeid name 
  - ----------------------------- 
 
 1 1样品1 
 2 1样品1 
 3 1样品1 
 4 2样品2 
 5 2样品2 
 7 2样品2 
 8 3样品3 
 9 3样品3 
 10 3样品3 
 11 4样品4 
 12 4样品4 
 13 5样品5 
 14 5样品5 
  
 

，并且我愿意为列表的id例如（1,2,3）

 
  id typeid名称
 --------------------- 
 
 1 1样本1 
 1 2样本1 
 2 4样本2 
 2 5样本2 
 3 8样本3 
 3 9样本3 
 

这是我的查询

 
从产品中选择p。*，g。* p内连接库g ON p.id = g.typeid其中p.id in（3,4,5）group by typeid  
 

这里是真实的结构 sqlfiddle链接

解决方案

  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）
  

编辑：

试试这个 SQL Fiddle Demo

  SELECT p.id，g.typeid，g.id，g.name 
 FROM产品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 
  

用于替换 group by typeid

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      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

解决方案

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

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

这篇关于mysql从每个组中选择2行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！