我有一个名为“ news_box”的表。那有3场。“ channel_id”，“ news_id”，“ read”
channel_id和news_id一起是主键。
数据样本可以这样做：
channel_id | news_id | read
-----------|---------|-----
1 | 1 | 0
1 | 2 | 0
2 | 3 | 0
3 | 4 | 0
2 | 5 | 0
3 | 6 | 0
3 | 7 | 0
3 | 8 | 0
3 | 9 | 0
1 | 10 | 0
1 | 11 | 0
1 | 12 | 0
2 | 13 | 0
3 | 14 | 0
2 | 15 | 0
3 | 16 | 0
3 | 17 | 0
3 | 18 | 0
3 | 19 | 0
1 | 20 | 0
现在我想为每个“ channel_id”获取3行“ news_id”。“ news_id”和“ channel_id”与自身表结合。
结果样本可以这样：
channel_id | news_id | read
-----------|---------|-----
1 | 1 | 0
1 | 2 | 0
2 | 3 | 0
3 | 4 | 0
2 | 5 | 0
3 | 6 | 0
3 | 7 | 0
1 | 10 | 0
2 | 13 | 0
最好的祝福。
回答：
set @num := 0, @group := 0;
select channel_id, `news_id`, `read`
from
(
select channel_id, `news_id`, `read`,
@num := if(@group = `channel_id`, @num + 1, 1) as row_number,
@group := `channel_id` as dummy
from news_box
order by `channel_id`
) as x where x.row_number <= 3;
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句