+----+---------+
| id | content |
+----+---------+
| 8 | 8 |
| 14 | 8 |
| 17 | 8 |
| 9 | 7 |
| 15 | 7 |
| 16 | 7 |
| 11 | 6 |
| 12 | 6 |
| 13 | 6 |
| 18 | 6 |
| 19 | 6 |
+----+---------+
就是content相同的数据分别取3条,比如上面(content=8,7,6的分别取3条就足够了。)
结果:
+----+---------+
| id | content |
+----+---------+
| 8 | 8 |
| 14 | 8 |
| 17 | 8 |
| 9 | 7 |
| 15 | 7 |
| 16 | 7 |
| 11 | 6 |
| 12 | 6 |
| 13 | 6 |
+----+---------+
求助,非常感谢!sql弱势,记得在sql server中用row_number实现过,可是 刚用mysql不是很熟悉,万分感谢MySQLSQL
| id | content |
+----+---------+
| 8 | 8 |
| 14 | 8 |
| 17 | 8 |
| 9 | 7 |
| 15 | 7 |
| 16 | 7 |
| 11 | 6 |
| 12 | 6 |
| 13 | 6 |
| 18 | 6 |
| 19 | 6 |
+----+---------+
就是content相同的数据分别取3条,比如上面(content=8,7,6的分别取3条就足够了。)
结果:
+----+---------+
| id | content |
+----+---------+
| 8 | 8 |
| 14 | 8 |
| 17 | 8 |
| 9 | 7 |
| 15 | 7 |
| 16 | 7 |
| 11 | 6 |
| 12 | 6 |
| 13 | 6 |
+----+---------+
求助,非常感谢!sql弱势,记得在sql server中用row_number实现过,可是 刚用mysql不是很熟悉,万分感谢MySQLSQL
if OBJECT_ID('tempdb.dbo.#Content') is not null drop table #Content
create table #Content
(
id int,
content int
)insert into #Content(id,content)values
('8','8'),('14','8'),('17','8'),('9','7'),('15','7'),
('16','7'),('11','6'),('12','6'),('13','6'),('18','6'),('19','6')select
id,content
from
(
select
ROW_NUMBER() over (PARTITION by content order by id) row,
id,content
from #Content
) a
where a.row<=3
order by content,idif OBJECT_ID('tempdb.dbo.#Content') is not null drop table #Content
from tb A
where 3>(select count(*) from tb content=A.content and A.id>id)
A1.ID>ID)
[征集]分组取最大N条记录方法征集,及散分....
from
(
select t1.id,
t1.content,
case when @vcontent=t1.content then @num:=@num+1
else @num:=1
end as num,
@vcontent:=t1.content
from t0530 t1,
(select @vcontent:=0,@num:=0) t2
order by t1.content
) T
where T.num <=3扩展阅读