我有下一个查询:
SELECT a.id, a.brand_id
FROM articles a
WHERE a.deleted=0 AND a.brand_id IN (5,6)
LIMIT 4
如何从IN语句中命名的所有brand_id中获取4篇文章?例如,我想从brand_id=5得到2篇文章,从brand_d=6得到2篇
您可以使用union all
(
SELECT a.id, a.brand_id
FROM articles a
WHERE a.deleted=0 AND a.brand_id = 5 limit 2
)
union all
(
SELECT a.id, a.brand_id
FROM articles a
WHERE a.deleted=0 AND a.brand_id = 6 limit 2
)
UPDATE,这可以使用m-per-group逻辑来实现,其中一种方法是-
考虑表格
mysql> select * from articles ;
+------+----------+---------+
| id | brand_id | deleted |
+------+----------+---------+
| 1 | 5 | 0 |
| 2 | 6 | 0 |
| 3 | 2 | 0 |
| 4 | 4 | 1 |
| 5 | 5 | 0 |
| 6 | 5 | 1 |
| 7 | 5 | 0 |
| 8 | 6 | 0 |
| 9 | 4 | 0 |
| 10 | 4 | 0 |
| 11 | 4 | 1 |
| 12 | 6 | 0 |
| 13 | 5 | 1 |
| 14 | 5 | 0 |
+------+----------+---------+
因此,通过下面的查询,将每个组返回n-
select
id,
brand_id
from (
select
id,
brand_id,
@r := if(@brand = brand_id,@r+1,1) as row_num,
@brand:= brand_id
from articles,(select @r:=0,@brand:='')rr
where
brand_id in (4,5,6)
and deleted = 0
order by brand_id
)x
where x.row_num <=2 limit 6;
+------+----------+
| id | brand_id |
+------+----------+
| 9 | 4 |
| 10 | 4 |
| 1 | 5 |
| 5 | 5 |
| 2 | 6 |
| 8 | 6 |
+------+----------+
6 rows in set (0.00 sec)
因此,这里的限制将始终是IN
子句*2 内的项目数
SELECT a.id, a.brand_id
FROM articles a
WHERE a.deleted=0 AND a.brand_id=5
LIMIT 2
UNION ALL
SELECT a.id, a.brand_id
FROM articles a
WHERE a.deleted=0 AND a.brand_id=6
LIMIT 2