mysql中group by之后想取到一组中的第二条记录怎么办？

[ljz_1c]

mysql中group by之后想取到一组中的第二条记录怎么办？

如上图，如何用mysql实现？

[峰峰517]

先写一个获取第二个元素的聚合函数，假设是secitem select * from table where signtime in (select secitem(signtime) from table group by name)

[峰峰517]

刚才那种方法有点问题，另一种方法:
Select * from table a where 1=(select count(*) from table where a.name=name and a.time >time)

[zjcxc]

去掉每组最小的一条，从剩下的数据中取每组最小的一条就可以了

SELECT * FROM tb
WHERE baoxian_id IN (
	SELECT min(baoxian_id) FROM tb
	WHERE baoxian_id NOT IN (
		SELECT min(baoxian_id) FROM tb GROUP BY name)
	GROUP BY name)
ORDER BY name;

[Gunlance]

引用 3 楼 zjcxc 的回复:

去掉每组最小的一条，从剩下的数据中取每组最小的一条就可以了

SELECT * FROM tb
WHERE baoxian_id IN (
	SELECT min(baoxian_id) FROM tb
	WHERE baoxian_id NOT IN (
		SELECT min(baoxian_id) FROM tb GROUP BY name)
	GROUP BY name)
ORDER BY name;

那如果这些记录分组之后每组都有N个，我要取每组的第N/2个数呢？

[zjcxc]

select * from(select id, name, @id:=if(@name=name,@id+1,0) as _r, @name:=name as _n from tb,(select @id:=0, @name:=null)b order by name) aa where _r % 2;