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的話是可以返回的。
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
舉報