3 回答
![?](http://img1.sycdn.imooc.com/533e4c9c0001975102200220-100-100.jpg)
TA贡献1784条经验 获得超9个赞
首先你这个表里有一个小问题,你的goods_addtime这个字段最好使用time型。
然后你的这个需求可以这么写:
SELECT A1.*
FROM goods AS A1
INNER JOIN (SELECT A.category_id,A.goods_addtime
FROM goods AS A
LEFT JOIN goods AS B
ON A.category_id = B.category_id
AND A.goods_addtime <= B.goods_addtime
GROUP BY A.category_id,A.goods_addtime
HAVING COUNT(B.goods_addtime) <= 3
) AS B1
ON A1.category_id = B1.category_id
AND A1.goods_addtime = B1.goods_addtime
ORDER BY A1.category_id,A1.goods_addtime DESC
我这个语句也有一点问题。如果你这个goods_addtime字段坚持要用int的话,那我这个语句没法返回正确结果。但是要是改成time的话是可以返回的。
![?](http://img1.sycdn.imooc.com/5333a207000118af02200220-100-100.jpg)
TA贡献2021条经验 获得超8个赞
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
- 3 回答
- 0 关注
- 225 浏览
添加回答
举报