一条SQL语句的疑问
表如下:
id memberID
12 6
13 7
14 7
15 7
16 8
17 8
18 9
id为自增，要求根据memberID拿出每人最近三条记录,如果超过三条也拿三条，低于三条只拿一条
---------
create table #i(id int,memberid int)
insert into #i values(12,6)
insert into #i values(13,7)
insert into #i values(14,7)
insert into #i values(10,7)
insert into #i values(15,7)
insert into #i values(16,8)
insert into #i values(17,8)
insert into #i values(18,9)
insert into #i values(19,7)
select * from #i aa where ((select count(*) from #i bb where aa.memberid=bb.memberid and bb.id> =aa.id) <=3 and (select count(*) from #i cc where aa.memberid=cc.memberid)> =3 )
or
((select count(*) from #i bb where aa.memberid=bb.memberid and bb.id> =aa.id)=1 and (select count(*) from #i cc where aa.memberid=cc.memberid) <3 )
---------------------
12 6
14 7
15 7
17 8
18 9
19 7
请问bb.id> =aa.id的含义是什么？
[解决办法]
10,13,14,15,19
对于以上每个值,因为aa和bb是两个相同的表,只有aa.id为14,15,19时,bb.id> aa.id取出的count(*)才为3以内的数值
