問(wèn)題描述

問(wèn)題解答

下面是SQL Server的寫(xiě)法

查詢5個(gè)分類，每個(gè)分類最多返回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實(shí)際多余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，沒(méi)有ROW_NUMBER，可以參考下面這樣實(shí)現(xiàn)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：

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

不知道你是不是要進(jìn)行分組，然后取各個(gè)分組5個(gè)？Here you go