多匹配场景下仅按需关联AID的SQL查询优化需求

核心逻辑 #

先按Thickness、Width、Grade、Length四个字段分组统计记录数：

• 若分组仅1条记录：直接返回该记录的pID，无需校验AID
• 若分组存在多条记录：仅返回与指定AID匹配的唯一记录的pID

查询SQL #

WITH product_groups AS (
    SELECT 
        pID,
        Thickness,
        Width,
        Grade,
        Length,
        AID,
        -- 计算当前四字段组合的总记录数
        COUNT(*) OVER (PARTITION BY Thickness, Width, Grade, Length) AS group_count
    FROM merged_products
)
SELECT pID
FROM product_groups
WHERE 
    Thickness = ? 
    AND Width = ? 
    AND Grade = ? 
    AND Length = ?
    AND (
        group_count = 1 
        OR (group_count > 1 AND AID = ?)
    );

临时表结构 #

CREATE TABLE merged_products (
    pID INT PRIMARY KEY,
    Thickness DECIMAL(10,2),
    Width DECIMAL(10,2),
    Grade VARCHAR(50),
    Length DECIMAL(10,2),
    AID INT
);

测试数据 #

INSERT INTO merged_products (pID, Thickness, Width, Grade, Length, AID) VALUES
(1, 1.5, 100.0, 'A', 200.0, 101),
(2, 1.5, 100.0, 'A', 200.0, 102),
(3, 2.0, 150.0, 'B', 300.0, 103),
(4, 1.5, 100.0, 'A', 200.0, 103),
(5, 3.0, 200.0, 'C', 400.0, NULL);

测试场景与期望输出 #

场景1：四字段匹配单条记录 #

查询参数：Thickness=2.0, Width=150.0, Grade='B', Length=300.0, AID=100
期望输出：3

说明：四字段仅匹配pID3，无需校验AID，直接返回

场景2：四字段匹配多条，AID筛选唯一项 #

查询参数：Thickness=1.5, Width=100.0, Grade='A', Length=200.0, AID=101
期望输出：1

说明：四字段匹配pID1/2/4共3条，通过AID=101筛选出唯一匹配的pID1

场景3：四字段匹配多条，AID无匹配 #

查询参数：Thickness=1.5, Width=100.0, Grade='A', Length=200.0, AID=999
期望输出：无记录

说明：多匹配场景下必须校验AID，无匹配项则返回空

场景4：四字段匹配单条，AID不匹配 #

查询参数：Thickness=3.0, Width=200.0, Grade='C', Length=400.0, AID=100
期望输出：5

说明：单匹配场景下忽略AID不匹配的情况，直接返回唯一记录

内容的提问来源于stack exchange，提问作者Dizzy49