id name time 1 a 2015-01-01 1 a 2015-02-01 1 a 2015-03-01 2 b 2015-01-01 2 b 2015-02-01 2 b 2015-03-01 2 b 2015-03-01 2 b 2015-03-01 3 c 2015-01-01 3 c 2015-01-01 3 c 2015-01-02 3 c 2015-01-02
当id 相同的情况下,取前三个最小时间
select * from test a where 3 >= (select count(1) from test b where a.id = b.id and a.time >= b.time)
但是,这么写会出一个问题,当id = 2 的时候, 本人只能取到前两个时间, 原因是从第三时间开始，时间是相等的,这种情况下.这样写，不知道为什么不能取到第三个..
PS: 本人要的是,当id=2的时候,不管时间能否相等,本人只取前3个..时间相等,任取一个.
本人要的结果:
id name time 1 a 2015-01-01 1 a 2015-02-01 1 a 2015-03-01 2 b 2015-01-01 2 b 2015-02-01 2 b 2015-03-01 3 c 2015-01-01 3 c 2015-02-01 3 c 2015-01-02
解决方案
60
select * from test a where 3 >= (select count(1) from test b where a.id = b.id and (a.time > b.time or (a.time = b.time and id>=b.id)))
20
set @rn=0;
set @name1=””;
select t1.id,t1.name,t1.time from (select id,name,IF(@name1 = name, @rn := @rn + 1,@rn := 1) as rn,@name1 := name as name1,time from t) t1 where t1.rn<=3;
set @name1=””;
select t1.id,t1.name,t1.time from (select id,name,IF(@name1 = name, @rn := @rn + 1,@rn := 1) as rn,@name1 := name as name1,time from t) t1 where t1.rn<=3;
CodeBye 版权所有丨如未注明 , 均为原创丨本网站采用BY-NC-SA协议进行授权 , 转载请注明分组求前N(不要转发 精华帖的分组求N,跟那里面有点不一样)！