两个表,一对多关系,用right join 能够拿出很多记录,
现在想问一下各位,能不能用一条语句拿出对应的前几条记录,
例如:
A -------A1
A -------A2
A -------A3
A -------A4
B -------B1
B -------B2
B -------B3
B -------B4我要的结果是分别拿出前几条,如:
A -------A1
A -------A2
B -------B1
B -------B2
现在想问一下各位,能不能用一条语句拿出对应的前几条记录,
例如:
A -------A1
A -------A2
A -------A3
A -------A4
B -------B1
B -------B2
B -------B3
B -------B4我要的结果是分别拿出前几条,如:
A -------A1
A -------A2
B -------B1
B -------B2
select * from (
select *,(select count(*) from tt where a.id<=id) as px from tt a)
where px<=2
select * from company a
where (select count(*) from company where dep=a.dep and a.id<=id)<=2
例如:
A -------A1
A -------A2
A -------A3
A -------A4
B -------B1
B -------B2
B -------B3
B -------B4
C -------C1
C -------C2
...我要的结果是分别拿出前几条,如:
A -------A1
A -------A2
B -------B1
B -------B2
C -------C1
C -------C2
...
通过 A right join B 之后得到很多记录,而现在只要保留各自对应关系的前N条即可。
谢谢。
Pid Name
1 Peter
2 Sam
3 JoeB表:
Cid Pid Course
1 1 English
2 1 Chinese
3 1 Math
4 2 Chinese
5 2 English
6 3 English
7 3 Chinese
8 3 Math
9 3 Art期待结果:
A.Pid A.Name Cid Pid Course
1 Peter 1 1 English
1 Peter 2 1 Chinese
2 Sam 4 2 Chinese
2 Sam 5 2 English
3 Joe 6 3 English
3 Joe 7 3 Chinese
where (select count(*) from bbb where cid<=b.cid and b.pid=pid)<=2
已经说过,除非有唯一标识的字段才行,B表中的CID是唯一的。
select A.Pid,A.Name,b.Cid,b.Pid,b.Course
from aaa a right join bbb b on a.pid=b.pid
right join bbb c on c.cid <=b.cid and c.pid=b.pid
group by A.Pid,A.Name,b.Cid,b.Pid,b.Course
having count(b.cid)<=2