create table a
(
id int
);
create table b
(
id int,
aid int
)
insert into a values(1);
insert into a values(2);
insert into a values(3);
insert into a values(4);
insert into b values(11,1);
insert into b values(12,1);
insert into b values(13,1);
insert into b values(21,2);
insert into b values(22,2);
insert into b values(23,2);
a表为主表,b表的aid对应的a表id想从b表查询出每个aid的前两条纪录
不知描述清楚了没,在线等
from a left join b on a.id=b.aid
where 2>(select count(*) from b bb where b.aid=bb.aid and b.id<bb.id);
/*
+------+------+
| id | id |
+------+------+
| 1 | 12 |
| 1 | 13 |
| 2 | 22 |
| 2 | 23 |
| 3 | NULL |
| 4 | NULL |
+------+------+
*/
http://topic.csdn.net/u/20091231/16/2f268740-391e-40f2-a15e-f243b2c925ab.html?25791
select * from b t1 where
2>select count(*) from b t2 where t2.aid=t1.aid and t2.id<t1.id
order by t1.aid,t1.id;
[征集]分组取最大N条记录方法征集,及散分....
-> from b t
-> where 2>(select count(*) from b where aid=t.aid and b.id>t.id);
+------+------+
| id | aid |
+------+------+
| 12 | 1 |
| 13 | 1 |
| 22 | 2 |
| 23 | 2 |
+------+------+
4 rows in set (0.03 sec)mysql>