请问怎么取出每个类别(category_id)产品的最近添加的(goods_addtime)三条数？

Question

若表结构如下：
create table goods(
goods_id int primary key,
category_id int,
goods_name varchar(20),
goods_addtime int unsigned
)engine myisam charset utf8;

Answer 1

首先你这个表里有一个小问题，你的goods_addtime这个字段最好使用time型。然后你的这个需求可以这么写：SELECT A1.*FROM goods AS A1INNER JOIN (SELECT A.category_id,A.goods_addtimeFROM goods AS ALEFT JOIN goods AS BON A.category_id = B.category_idAND A.goods_addtime <= B.goods_addtimeGROUP BY A.category_id,A.goods_addtimeHAVING COUNT(B.goods_addtime) <= 3) AS B1ON A1.category_id = B1.category_idAND A1.goods_addtime = B1.goods_addtimeORDER BY A1.category_id,A1.goods_addtime DESC我这个语句也有一点问题。如果你这个goods_addtime字段坚持要用int的话，那我这个语句没法返回正确结果。但是要是改成time的话是可以返回的。

Answer 2

SELECT * from (SELECT t.*,@num := if(@category_id = t.category_id, @num + 1,1) as cal_rank,@category_id:=t.category_id from(SELECT * from goods ORDER BY category_id asc ,goods_addtime desc) t,(SELECT @num := 0) t1,(SELECT @category_id := 0) t2) t3 where t3. cal_rank <4

Answer 3

select * from goods ordery goods_addtime desc limit 3;