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

Answer 1

下面是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 1
2 B 1 2
3 C 2 1
4 D 2 2
5 E 3 1
6 R 3 2
7 G 4 1
8 H 4 2
9 R 4 3
10 GG 4 4
11 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

Answer 2

没怎么看懂。类似这样where 条件1 and 条件2 and 条件3 and 条件4 and 条件5 limit 25可以吗

Answer 3

不知道你是不是要进行分组，然后取各个分组5个？Here you go