如何在单条SQL中遍历多客户代码并各执行LIMIT 1查询

现有一条SQL语句，可从TABLEA表中根据多项条件为特定client_code获取一行（LIMIT 1）记录，具体语句如下：

SELECT
    `TABLEA`.`client_code`
FROM
    `TABLEA`
WHERE
(
    `TABLEA`.`accounting_date` = 202408
    AND (
        `TABLEA`.`FLAG1` = ''
        OR `TABLEA`.`FLAG1` IS NULL
    )
    AND `TABLEA`.`client_code` ='A12'
    AND (
        `TABLEA`.`FLAG2` = ''
        OR `TABLEA`.`FLAG2` IS NULL
    )
    AND (
        `TABLEA`.`FLA3` = ''
        OR `TABLEA`.`FLA3` IS NULL
    )
    AND (
        `TABLEA`.`payment_closing_flag` = ''
        OR `TABLEA`.`payment_closing_flag` IS NOT NULL
    )
    AND NOT (
        `TABLEA`.`FLAG4` = 1
        AND `TABLEA`.`FLAG4` IS NOT NULL
        AND `TABLEA`.`record_status` = 'D'
        AND `TABLEA`.`record_status` IS NOT NULL
        AND `TABLEA`.`ABC_CODE` IN (0, 370, 380, 390, 400, 410, 420, 900)
        AND `TABLEA`.`ABC_CODE` IS NOT NULL
    )
) LIMIT 1;

该语句仅适用于单个client_code，现需对多个client_code（如'A12'、'A13'、'A14'等）执行该查询，每个client_code返回一行结果。

约束条件 #

• 单客户数据量达数百万行，无法使用GROUP BY或DISTINCT，仅需验证每个客户代码是否存在符合条件的记录；
• 性能要求极高，GROUP BY或DISTINCT对该数据集来说速度过慢。

请问是否可在单条SQL查询或单次执行块中遍历多个客户代码，为每个代码执行上述LIMIT 1查询？

完全可以通过单条SQL或单次执行块实现需求，以下是几种高性能的实现方式：

方法1：用UNION ALL拼接单个查询 #

直接为每个目标client_code单独编写原查询，用UNION ALL拼接。这种方式复用原查询的高效执行计划（若有合适索引），每个子查询找到符合条件的第一行就停止扫描，无额外聚合开销。

示例代码：

SELECT `client_code` FROM `TABLEA` WHERE (
    `accounting_date` = 202408
    AND (`FLAG1` = '' OR `FLAG1` IS NULL)
    AND `client_code` = 'A12'
    AND (`FLAG2` = '' OR `FLAG2` IS NULL)
    AND (`FLA3` = '' OR `FLA3` IS NULL)
    AND (`payment_closing_flag` = '' OR `payment_closing_flag` IS NOT NULL)
    AND NOT (
        `FLAG4` = 1 AND `FLAG4` IS NOT NULL
        AND `record_status` = 'D' AND `record_status` IS NOT NULL
        AND `ABC_CODE` IN (0, 370, 380, 390, 400, 410, 420, 900) AND `ABC_CODE` IS NOT NULL
    )
) LIMIT 1
UNION ALL
SELECT `client_code` FROM `TABLEA` WHERE (
    `accounting_date` = 202408
    AND (`FLAG1` = '' OR `FLAG1` IS NULL)
    AND `client_code` = 'A13'
    AND (`FLAG2` = '' OR `FLAG2` IS NULL)
    AND (`FLA3` = '' OR `FLA3` IS NULL)
    AND (`payment_closing_flag` = '' OR `payment_closing_flag` IS NOT NULL)
    AND NOT (
        `FLAG4` = 1 AND `FLAG4` IS NOT NULL
        AND `record_status` = 'D' AND `record_status` IS NOT NULL
        AND `ABC_CODE` IN (0, 370, 380, 390, 400, 410, 420, 900) AND `ABC_CODE` IS NOT NULL
    )
) LIMIT 1
UNION ALL
SELECT `client_code` FROM `TABLEA` WHERE (
    `accounting_date` = 202408
    AND (`FLAG1` = '' OR `FLAG1` IS NULL)
    AND `client_code` = 'A14'
    AND (`FLAG2` = '' OR `FLAG2` IS NULL)
    AND (`FLA3` = '' OR `FLA3` IS NULL)
    AND (`payment_closing_flag` = '' OR `payment_closing_flag` IS NOT NULL)
    AND NOT (
        `FLAG4` = 1 AND `FLAG4` IS NOT NULL
        AND `record_status` = 'D' AND `record_status` IS NOT NULL
        AND `ABC_CODE` IN (0, 370, 380, 390, 400, 410, 420, 900) AND `ABC_CODE` IS NOT NULL
    )
) LIMIT 1;

说明：若某个client_code无符合条件的记录，对应子查询不会返回行。若需确保每个目标client_code都有一行结果（哪怕无匹配），可给每个子查询追加UNION ALL SELECT '目标代码' WHERE NOT EXISTS (...)逻辑，但会增加少量开销。

方法2：IN条件+窗口函数快速筛选 #

若数据库支持窗口函数（如MySQL 8.0+、PostgreSQL等），可先筛选出所有目标client_code的符合条件记录，再用ROW_NUMBER()为每个客户标记行号，取第一行。这种方式仅扫描一次目标数据范围（若有合适索引），避免多次查询。

示例代码：

WITH filtered AS (
    SELECT 
        `client_code`,
        ROW_NUMBER() OVER (PARTITION BY `client_code` ORDER BY (SELECT NULL)) AS rn
    FROM `TABLEA`
    WHERE 
        `accounting_date` = 202408
        AND (`FLAG1` = '' OR `FLAG1` IS NULL)
        AND `client_code` IN ('A12', 'A13', 'A14')
        AND (`FLAG2` = '' OR `FLAG2` IS NULL)
        AND (`FLA3` = '' OR `FLA3` IS NULL)
        AND (`payment_closing_flag` = '' OR `payment_closing_flag` IS NOT NULL)
        AND NOT (
            `FLAG4` = 1 AND `FLAG4` IS NOT NULL
            AND `record_status` = 'D' AND `record_status` IS NOT NULL
            AND `ABC_CODE` IN (0, 370, 380, 390, 400, 410, 420, 900) AND `ABC_CODE` IS NOT NULL
        )
)
SELECT `client_code` FROM filtered WHERE rn = 1;

优化建议：为表创建包含client_code和accounting_date的复合索引，让数据库快速定位目标客户的指定日期数据，避免全表扫描。

方法3：临时表存储目标客户+关联查询 #

若目标client_code数量较多，可先将代码存入临时表，再通过关联查询批量获取结果：

步骤1：创建临时表并插入目标客户 #

CREATE TEMPORARY TABLE target_clients (client_code VARCHAR(10) PRIMARY KEY);
INSERT INTO target_clients VALUES ('A12'), ('A13'), ('A14');

步骤2：关联查询获取结果 #

SELECT tc.client_code
FROM target_clients tc
LEFT JOIN LATERAL (
    SELECT 1
    FROM TABLEA a
    WHERE 
        a.client_code = tc.client_code
        AND a.accounting_date = 202408
        AND (a.FLAG1 = '' OR a.FLAG1 IS NULL)
        AND (a.FLAG2 = '' OR a.FLAG2 IS NULL)
        AND (a.FLA3 = '' OR a.FLA3 IS NULL)
        AND (a.payment_closing_flag = '' OR a.payment_closing_flag IS NOT NULL)
        AND NOT (
            a.FLAG4 = 1 AND a.FLAG4 IS NOT NULL
            AND a.record_status = 'D' AND a.record_status IS NOT NULL
            AND a.ABC_CODE IN (0, 370, 380, 390, 400, 410, 420, 900) AND a.ABC_CODE IS NOT NULL
        )
    LIMIT 1
) AS match_exists ON TRUE
WHERE match_exists.1 IS NOT NULL;

这种方式适合批量处理大量客户代码，LATERAL关联（或MySQL中的JOIN ... (SELECT ... LIMIT 1)）会为每个客户执行一次高效的小查询，性能接近单独执行每个查询的速度。

内容的提问来源于stack exchange，提问作者Abdul Rahman.