以eid,cid相同为一类,取出某个分类的前几条,怎么写?
eid cid value
1 1 1
1 1 2
1 1 3
1 2 1
1 2 2
1 2 3
1 3 1比如要取前2条
1 1 1
1 1 2
1 2 1
1 2 2
1 3 1
eid cid value
1 1 1
1 1 2
1 1 3
1 2 1
1 2 2
1 2 3
1 3 1比如要取前2条
1 1 1
1 1 2
1 2 1
1 2 2
1 3 1
from tb t
where
(select count(1) from tb where eid=t.eid and cid=t.cid and value<=t.value)<=2
+------+------+-------+
| eid | cid | value |
+------+------+-------+
| 1 | 1 | 1 |
| 1 | 1 | 2 |
| 1 | 1 | 3 |
| 1 | 2 | 1 |
| 1 | 2 | 2 |
| 1 | 2 | 3 |
| 1 | 3 | 1 |
+------+------+-------+
7 rows in set (0.03 sec)mysql>
mysql> select *
-> from t_xinlan1022 a
-> where 2>(select count(*) from t_xinlan1022 where eid=a.eid and cid=a.cidand `value`<a.value);
+------+------+-------+
| eid | cid | value |
+------+------+-------+
| 1 | 1 | 1 |
| 1 | 1 | 2 |
| 1 | 2 | 1 |
| 1 | 2 | 2 |
| 1 | 3 | 1 |
+------+------+-------+
5 rows in set (0.09 sec)mysql>
[征集]分组取最大N条记录方法征集
select *
from t_xinlan1022 a
where 2>(select count(*) from t_xinlan1022 where eid=a.eid and cid=a.cid and time<a.time);求出来的不对哦,求高手指点