mysql - 问一个简单的sql语句,查询一个商品中分类为 1 2 3 4 5 的 同时查询他们的5条

问题描述

问题解答

下面是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