MySQL IN子句：数据顺序与条件顺序不一致情况探究（二）

2. 临时表/派生表的使用

另一个常见的方法是使用一个临时表或派生表（也称为子查询）来存储IN子句中的 ID，并为这些 ID 添加一个序号，然后在外层查询中根据这个序号进行排序。

使用示例：

-- 新建临时表  CTE
WITH RouteOrder AS (  
    SELECT  
        route_id,  
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rn  -- 这里的ORDER BY (SELECT NULL)只是为了生成行号，实际顺序不重要  
    FROM (  
        SELECT route_id  
        FROM t_job_plan_route  
        WHERE job_plan_id = '1716'  
    ) AS job_routes  
)


SELECT  
    t.id,  
    t.project_id,  
    t.site_id,  
    t.route_version,  
    detail.route_name,  
    detail.route_length,  
    detail.task_area,  
    detail.task_address,  
    detail.task_segment  
FROM  
    t_site_route t  
    JOIN t_site site ON t.site_id = site.id AND t.site_version = site.site_version  
    LEFT JOIN t_site_route_detail detail ON detail.route_id = t.id AND detail.route_version = t.route_version  
    JOIN RouteOrder ro ON t.id = ro.route_id  -- 加入排序依据  
WHERE  
    t.id IN (SELECT route_id FROM RouteOrder)  -- 确保只选取已排序的ID  
ORDER BY  
    ro.rn;  -- 根据序号排序

2.1. CTE 简介

CTE(Common Table Expressions 公用表表达式) 是一种在 SQL 查询中定义临时结果集的方式。这个结果集在查询的范围内是可见的，就像是一个临时的视图或表一样。CTE 通常用于简化复杂的查询，使它们更易于阅读和维护。

CTE 是一个命名的临时结果集合，仅在单个 SQL 语句（select、insert、update 或 delete）的执行范围内存在。

与派生表类似的是：CTE 不作为对象存储，仅在查询执行期间持续。

与派生表不同的是：CTE 可以是自引用（递归CTE），也可以在同一查询中多次引用。

此外，与派生表相比，CTE 提供了更好的可读性和性能。

2.2. CTE 语法

CTE 的结构包括：名称、可选列列表和定义 CTE 的查询。定义 CTE 后，可以像 select、insert、update、delete 或 create view 语句中的视图一样使用它。

with cte_name (column_list) as (
    query
  )

select * from cte_name;

query 中的列数必须与 column_list 中的列数相同。 如果省略 column_list，CTE 将使用定义 CTE 的查询的列列表。

2.3. 示例讲解

1. WITH RouteOrder AS (...):

• • 这定义了一个名为RouteOrder的CTE。
  • CTE的内容由括号内的查询指定。

1. 内部查询（job_routes子查询）:

SELECT route_id FROM t_job_plan_route WHERE job_plan_id = '1716'

• • 这个查询从t_job_plan_route表中选取route_id，条件是job_plan_id等于'1716'。
  • 结果是一个包含route_id的临时表或结果集，这里被命名为job_routes（虽然这个名称在CTE外部是不可见的，但它有助于在编写和理解查询时提供清晰的上下文）。

1. 外部查询（RouteOrder CTE的定义）:

SELECT    
  route_id,    
  ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rn  -- SELECT NULL 返回一个空值列

• • 这个查询从job_routes（即内部查询的结果）中选取route_id。
  • 它还使用ROW_NUMBER()窗口函数为每个route_id分配一个唯一的行号（rn）。
  • ROW_NUMBER() OVER (ORDER BY (SELECT NULL))的意思是，为每一行分配一个行号，但由于ORDER BY (SELECT NULL)，实际上并不指定任何排序顺序。这意味着行号的分配顺序是任意的，取决于数据库引擎的内部实现。然而，在这个特定的场景中，我们并不关心行号的分配顺序，因为我们将在外部查询中使用这些行号来确保结果集按照我们想要的顺序返回。