当前位置：首页 > article >正文

在SQL的SELECT中实现循环查找、双层和多层循环（迭代）查找 SQL如何实现编程语言的for循环查询 MySQL的Select子查询

article 2025/1/23 14:11:06

在SQL的SELECT中实现循环查找、双层和多层循环（迭代）查找 SQL如何实现编程语言的for循环查询 MySQL的Select子查询。

一、背景

在查询表极大，可能需要使用特定的查询索引的时候，使用SELECT子查询会比使用Inner Join查询效率要高。

如，我需要知道美国站点的在线listing数。普通的查询语句，直接使用Inner Join查询会由于店铺数量出现极大的查询性能问题。若店铺少，可能会几秒，随着店铺多起来，查询等待时间可能成百上千倍的增长，而不是预期的线性增长。甚至很多时候，会直接卡死。（原表上亿行）。

使用循环式查找则是将店铺表作为主表，然后嵌套SELECT子查询来实现。有点类似于每个店铺都是用编程语言的for循环，来逐条执行。这种方式快速且稳定。当表格极大，且需要用到特定的索引时，查询性能提升尤为明显。（注意，这里店铺表才几百个店铺，而listing有上亿）

二、一层迭代（一层循环）

①需求：查找以深圳开头、国家为美国的店铺在平台的美国站点的在线listing数。

②、Inner Join实现

/*
普通Inner Join。

时间：45.067
*/
select
    os.OrderSourceID
    , count(1) listing表在线listing数
from sys_ordersource os 
inner join m_ebay_listing el on el.OrderSourceId=os.OrderSourceID
where os.CustomerID=1
    and os.OrderSourceType=2
    and os.OrderSourceCountry = 'US'
    and os.OrderSourceName like '深圳%'
    and el.SiteCode='US'
    and el.OnlineStatus=1
group by os.OrderSourceId
;

③、嵌套查询实现、迭代查询的子查询

/*
SELECT 子查询

时间：6.570
*/
select
    os.OrderSourceID
    , (
        select 
           count(1) 
        from m_ebay_listing el
        where el.OrderSourceId=os.OrderSourceID
        and el.SiteCode='US'
        and el.OnlineStatus=1
    ) listing表在线listing数
from sys_ordersource os 
where os.CustomerID=1
    and os.OrderSourceType=2
    and os.OrderSourceCountry = 'US'
    and os.OrderSourceName like '深圳%'
;

三、两层迭代（两层循环）

①需求：在2025年1月15号到2025年1月20号，等级为3、国家为美国的深圳店铺的每天刊登成功的listing数

注：ap_autopublish_ebay_queue 队列表有几亿的数据。

②单层with的两层迭代查询

/*
方式一、美国站点的中级店铺，在1月15号到1月20号每日的刊登成功量。

时间：1.914s
居然比下面那个快这么多。
*/
WITH RECURSIVE temp_dateTable AS (
  SELECT 20250115 AS datekey
  UNION ALL
  SELECT date_format(date_add(CONVERT(datekey, CHAR),interval 1 day), "%Y%m%d")+0
  FROM temp_dateTable
  WHERE datekey < 20250120
)
select
        dt.datekey
        , os.OrderSourceID
        , (
            select
                count(1) 数量
            from ap_autopublish_ebay_queue peq
            where peq.CustomerId=1
                and peq.MarketId=2
                and peq.date=dt.datekey
                and peq.OrderSourceId = os.OrderSourceID
                and peq.Status='success'
        ) 刊登成功数
from temp_dateTable dt
inner join sys_ordersource os on 1=1
inner join sys_ebay_odersource_level eol on os.CustomerID=eol.CustomerID and os.OrderSourceID=eol.OrderSourceID
where os.CustomerID=1
        and os.OrderSourceType=2
        and os.OrderSourceName like '直发%'
        and os.OrderSourceCountry='US'
        and eol.StoreLevel=3

③两层with的两层迭代查询：可借鉴用来实现多层迭代查询

/*
方式二、美国站点的中级店铺，在1月15号到1月20号每日的刊登成功量。

时间：17.343s
*/
WITH RECURSIVE temp_dateTable AS (
  SELECT 20250115 AS datekey
  UNION ALL
  SELECT date_format(date_add(CONVERT(datekey, CHAR),interval 1 day), "%Y%m%d")+0
  FROM temp_dateTable
  WHERE datekey < 20250120
), tos as (
        select 
                os.OrderSourceID
        from sys_ordersource os
        inner join sys_ebay_odersource_level eol 
            on os.CustomerID=eol.CustomerID and os.OrderSourceID=eol.OrderSourceID
        where os.CustomerID=1
            and os.OrderSourceType=2
            and os.OrderSourceName like '直发%'
            and os.OrderSourceCountry='US'
            and eol.StoreLevel=3
)
select
        dt.datekey
        , tos.OrderSourceID
        , (
            select
                count(1) 数量
            from ap_autopublish_ebay_queue peq
            where peq.CustomerId=1
                and peq.MarketId=2
                and peq.date=dt.datekey
                and peq.OrderSourceId = tos.OrderSourceID
                and peq.Status='success'
        ) 刊登成功数
from temp_dateTable dt
inner join tos on 1=1