問題描述

問題解答

下面是SQL Server的寫法

查詢5個分類，每個分類最多返回5條

;WITH good(id,[name],cateId)AS( SELECT 1,’A’,1 UNION ALL SELECT 2,’B’,1 UNION ALL SELECT 3,’C’,2 UNION ALL SELECT 4,’D’,2 UNION ALL SELECT 5,’E’, 3 UNION ALL SELECT 6,’R’,3 UNION ALL SELECT 7,’G’,4 UNION ALL SELECT 8,’H’,4 UNION ALL SELECT 9,’R’,4 UNION ALL SELECT 10,’GG’,4 UNION ALL SELECT 11,’HH’,4 UNION ALL SELECT 12,’RR’,4 UNION ALL SELECT 13,’Y’,4 )SELECT * FROM ( SELECT *,ROW_NUMBER()OVER(PARTITION BY g.cateId ORDER BY id) AS rn FROM good AS g WHERE g.cateId BETWEEN 1 AND 5) t WHERE rn<=5

分類4實際多余5條，但查詢僅返回五條id name cateId rn----------- ---- ----------- --------------------1 A 1 12 B 1 23 C 2 14 D 2 25 E 3 16 R 3 27 G 4 18 H 4 29 R 4 310 GG 4 411 HH 4 5

如果不同分類要返回不同的行，下面是分類1，2，3最多返回2條其他最多返回5條

;WITH good(id,[name],cateId)AS( SELECT 1,’A’,1 UNION ALL SELECT 2,’B’,1 UNION ALL SELECT 3,’C’,2 UNION ALL SELECT 4,’D’,2 UNION ALL SELECT 5,’E’, 3 UNION ALL SELECT 6,’R’,3 UNION ALL SELECT 7,’G’,4 UNION ALL SELECT 8,’H’,4 UNION ALL SELECT 9,’R’,4 UNION ALL SELECT 10,’GG’,4 UNION ALL SELECT 11,’HH’,4 UNION ALL SELECT 12,’RR’,4 UNION ALL SELECT 13,’Y’,4 )SELECT * FROM ( SELECT *,ROW_NUMBER()OVER(PARTITION BY g.cateId ORDER BY id) AS rn FROM good AS g WHERE g.cateId BETWEEN 1 AND 5) t WHERE rn<=case WHEN cateId IN (1,2,3) then 2 ELSE 5 END

如果是MYSQL，沒有ROW_NUMBER，可以參考下面這樣實現ROW_NUMBER()OVER(PARTITION BY

SELECT @row_num := IF(@prev_value=cateId,@row_num+1,1) AS rn,id,[name],cateId ,@prev_value := cateId FROM good, (SELECT @row_num := 1) x, (SELECT @prev_value := ’’) y ORDER BY cateId, id回答2：

沒怎么看懂。類似這樣where 條件1 and 條件2 and 條件3 and 條件4 and 條件5 limit 25可以嗎

不知道你是不是要進行分組，然后取各個分組5個？Here you go