找出各类型中排第三名的记录的ID号,ID号小于此号的就是前2条。 select * from a t4, (select t1.type,t1.id from a t1,a t2 where t1.type=t2.type and t1.id>t2.id group by t1.type,t1.id having count(t2.ID)=2) t3 where t4.type=t3.type and t4.id<t3.id
这是每种类型取最新一条记录sql语句: select * from a where id in (select max(id) from a group by type ) 这是取第二新的语句: select * from a where id in ( select max(id) from a ,(select max(id) as maxid,type from a group by type ) b where a.type = b.type and a.id < b.maxid group by a.type )
让后将两部分union起来。 我觉得就取一条挺好的,效率比较高,取两条效率不高
icevi(按钮工厂) 的应该是 select t4.* from a t4, (select t1.type,t1.id from a t1,a t2 where t1.type=t2.type and t1.id>t2.id group by t1.type,t1.id having count(t2.ID)=2) t3 where t4.type=t3.type and t4.id<t3.id我感觉icevi(按钮工厂) 的方法比我的好,但不知道好多少,大家说说。
select *
from a t4,
(select t1.type,t1.id
from a t1,a t2
where t1.type=t2.type and t1.id>t2.id
group by t1.type,t1.id
having count(t2.ID)=2) t3
where t4.type=t3.type and t4.id<t3.id
select * from a where id in (select max(id) from a group by type )
这是取第二新的语句:
select * from a
where id in ( select max(id)
from a ,(select max(id) as maxid,type from a group by type ) b
where a.type = b.type and a.id < b.maxid
group by a.type )
让后将两部分union起来。
我觉得就取一条挺好的,效率比较高,取两条效率不高
select t4.*
from a t4,
(select t1.type,t1.id
from a t1,a t2
where t1.type=t2.type and t1.id>t2.id
group by t1.type,t1.id
having count(t2.ID)=2) t3
where t4.type=t3.type and t4.id<t3.id我感觉icevi(按钮工厂) 的方法比我的好,但不知道好多少,大家说说。
是应该改成select t4.* ,老兄眼好尖啊,哈哈