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

Question

查询一个商品中分类为 1 2 3 4 5 的商品
分别查询他们的5个条件但是同时限制5条
也就是25条结果
如何用一条sql写出来

例如
good表
id name cateId
1 A 1
2 B 1
3 C 2
4 D 2
5 E 3
6 R 3
7 G 4
8 H 4
9 R 4
10 Y 4

将条件

select * from good where cateId=1 limit 2  
     select * from good where cateId=2 limit 2
     select * from good where cateId=3 limit 2 

的结果用一条sql查询出来 不用union

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