mysql实现不用limit查询区间内的数据

前言

    在使用子查询的过程中，由于在嵌套语句中使用limit会出现This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'的报错，因此考虑是否存在不使用limit也能实现查询指定区间的内容的方法。

---

实现代码

SELECT cust_name FROM customers
WHERE cust_id IN
  (SELECT cust_id FROM orders 
   WHERE order_num IN 
    (SELECT order_num FROM 
      (SELECT order_num, ROW_NUMBER() OVER(ORDER BY item_price*quantity DESC) AS rownum FROM orderitems)c
     WHERE rownum < 4))
ORDER BY cust_name

    此代码实现的功能是取出购买金额最大的3位顾客的姓名，结果按降序排列。(购买金额的计算方式为item_price*quantity)

    原理是在orderitems表中用 ROW_NUMBER() 按购买金额(item_price*quantity)降序排列生成新列rownums，并利用此列和WHERE语句筛选出此时的前三条(也就是购买金额最大的3个)order_num；再用查询到的orderitems表中的order_num关联到orders表中的order_num，并以此为条件查询orders表中对应的cust_id；最后用查询到的orders表中的cust_id关联到customer表中的cust_id，并以此为条件查询customer表中对应的cust_name，完成任务。

建表语句如下，可供练习使用：

DROP TABLE IF EXISTS `OrderItems`;
CREATE TABLE IF NOT EXISTS `OrderItems`(
       order_num VARCHAR(255) NOT NULL COMMENT '商品订单号',
       item_price INT(16) NOT NULL COMMENT '售出价格',
       quantity INT(16) NOT NULL COMMENT '商品数量'
);
INSERT `OrderItems` VALUES ('a1',10,105),('a2',1,1100),('a2',1,200),('a4',2,1121),('a5',5,10),('a2',1,19),('a7',7,5);
 
 
DROP TABLE IF EXISTS `Customers`;
CREATE TABLE IF NOT EXISTS `Customers`(
       cust_id VARCHAR(255) NOT NULL COMMENT '客户id',
       cust_name VARCHAR(255) NOT NULL COMMENT '客户姓名'
);
INSERT `Customers` VALUES ('cust10','andy'),('cust1','ben'),('cust2','tony'),('cust22','tom'),('cust221','an'),('cust2217','hex');
 
DROP TABLE IF EXISTS `Orders`;
CREATE TABLE IF NOT EXISTS `Orders`(
  order_num VARCHAR(255) NOT NULL COMMENT '商品订单号',
  cust_id VARCHAR(255) NOT NULL COMMENT '顾客id'
);
INSERT `Orders` VALUES ('a1','cust10'),('a2','cust1'),('a3','cust2'),('a4','cust22'),('a5','cust221'),('a7','cust2217');

---

总结

    这次解决的问题是如何在不使用limit语句的前提下查询目标区间的数据，方法本质上是生成新列然后用新列作为查询的条件。

    避坑提醒：ROW_NUMBER() 属于窗口函数，是不能直接放在WHERE语句中的。