MySQL多表查询：如何仅输入一次条件替代多次重复输入

数据老陈

2026-8-1

实现方案

当然可以实现，以下是两种适配不同MySQL版本的解决方案，都只需要写一次查询条件：

方案一：适用于MySQL 8.0及以上（支持CTE和窗口函数）

通过公共表表达式（CTE）先合并三张表的数据，再统一过滤条件，最后用窗口函数给每个表的符合条件数据编号，取前1000条：

WITH combined AS (
    SELECT 'Table1' AS table_name, a, B FROM `Table 1`
    UNION ALL
    SELECT 'Table2' AS table_name, a, B FROM `Table 2`
    UNION ALL
    SELECT 'Table3' AS table_name, a, B FROM `Table 3`
),
ranked AS (
    SELECT 
        a,
        ROW_NUMBER() OVER (PARTITION BY table_name ORDER BY (SELECT NULL)) AS rn
    FROM combined
    WHERE a = 10 AND B = 2
)
SELECT a FROM ranked WHERE rn <= 1000;

逻辑说明：

combined CTE负责将三张表的数据合并，并标记每条数据所属的表
ranked CTE中统一写入过滤条件a=10 AND B=2，同时用ROW_NUMBER()按表分组编号
最后筛选出每个表编号≤1000的记录，和原查询效果一致

方案二：适用于MySQL 5.x版本（不支持CTE）

使用用户变量模拟分组编号，同样只写一次过滤条件：

SELECT a
FROM (
    SELECT 
        a,
        @rn := IF(@current_table = table_name, @rn + 1, 1) AS rn,
        @current_table := table_name
    FROM (
        SELECT 'Table1' AS table_name, a, B FROM `Table 1`
        UNION ALL
        SELECT 'Table2' AS table_name, a, B FROM `Table 2`
        UNION ALL
        SELECT 'Table3' AS table_name, a, B FROM `Table 3`
    ) AS combined
    CROSS JOIN (SELECT @current_table := '', @rn := 0) AS vars
    WHERE a = 10 AND B = 2
    ORDER BY table_name
) AS ranked
WHERE rn <= 1000;

逻辑说明：