to CrazyFor(吃素的鼹鼠): 你的方法选出的comid最多是3条,是不是把<=2改为<=1?,你的方法是能选出来了,但是缺点是太慢:(这个表很大的,少则上千条记录,多则上万条,10000x10000=1亿次查询:(
不过找某一个userid就快很多了,谢谢select * from 表 bb where bb.userid=1 and (select count(*) from 表 aa where aa.userid=bb.userid and aa.id>bb.id)<=2 order by userid,comid,addtime desc既然可以不用group by having, 我一直在那打转。
to CrazyFor(吃素的鼹鼠): 想了想还是不对,你的方法只是相对于select top 2,没有实现每一个存在的comid最多选2条,继续
我的是对userId取两条,改一下就可以对comid取两条了,
select * from table1 bb where bb.userid=1 and (select count(*) from table1 aa where aa.userid=1 and aa.comid=bb.comid and aa.id>bb.id)<=1 order by a,b,c desc这样对了吧
这道题描述的不够清楚!select * from [table] a where addtime in (select top 2 addtime from [table] where userid = a.userid and comid = a.comid order by comid,addtime desc ) order by comid,addtime descor:select * from [table] a where addtime in (select top 2 addtime from [table] where userid = a.userid order by comid,addtime desc ) order by comid,addtime desc
to playyuer(双规干部): 你的前面的那个是正确的,我用我的已存在的数据测试,发现你的方法没有CrazyFor(吃素的鼹鼠)的快,他所有的时间是你的一半。(我是让它把所有的userid的都符合要求的记录选出来)select * from [table] a where addtime in (select top 2 addtime from [table] where userid = a.userid and comid = a.comid order by comid,addtime desc ) order by userid,comid,addtime descselect * from table1 bb where (select count(*) from table1 aa where aa.userid=bb.useid and aa.comid=bb.comid and aa.id>bb.id)<=1 order by userid,comid,addtime desc我本来以为你的快一点的 :(
id userid comid addtime
6 1 2 2003-10-10 22:39:31
1 1 2 2003-10-10 22:09:32
2 1 3 2003-10-10 22:17:11
5 2 1 2003-10-10 22:33:45
4 2 1 2003-10-10 22:23:32
6 1 2 2003-10-10 22:39:31
1 1 2 2003-10-10 22:09:32
2 1 3 2003-10-10 22:17:11
你的方法选出的comid最多是3条,是不是把<=2改为<=1?,你的方法是能选出来了,但是缺点是太慢:(这个表很大的,少则上千条记录,多则上万条,10000x10000=1亿次查询:(
想了想还是不对,你的方法只是相对于select top 2,没有实现每一个存在的comid最多选2条,继续
from [table] a
where addtime in (select top 2 addtime
from [table]
where userid = a.userid
and comid = a.comid
order by comid,addtime desc
)
order by comid,addtime descor:select *
from [table] a
where addtime in (select top 2 addtime
from [table]
where userid = a.userid
order by comid,addtime desc
)
order by comid,addtime desc
你的前面的那个是正确的,我用我的已存在的数据测试,发现你的方法没有CrazyFor(吃素的鼹鼠)的快,他所有的时间是你的一半。(我是让它把所有的userid的都符合要求的记录选出来)select *
from [table] a
where addtime in (select top 2 addtime
from [table]
where userid = a.userid
and comid = a.comid
order by comid,addtime desc
)
order by userid,comid,addtime descselect * from table1 bb where (select count(*) from table1 aa where aa.userid=bb.useid and aa.comid=bb.comid and aa.id>bb.id)<=1 order by userid,comid,addtime desc我本来以为你的快一点的 :(
id userid comid addtime
1 1 2 2003-10-10 22:09:32
2 1 3 2003-10-10 22:17:11
3 2 1 2003-10-10 22:22:24
4 2 1 2003-10-10 22:23:32
5 2 1 2003-10-10 22:33:45
6 1 2 2003-10-10 22:39:31要求:选出所有包含指定的userid,且每一个相同的comid最多选出2条记录,
结果按照comid升序,addtime降序排序。(addtime有可能相同(重复),id不会重复)上面列子输出结果为:(如筛选的userid=1的话)id userid comid addtime
6 1 2 2003-10-10 22:39:31
1 1 2 2003-10-10 22:09:32
2 1 3 2003-10-10 22:17:11清楚了吧??
5 2 1 2003-10-10 22:33:45
4 2 1 2003-10-10 22:23:32