基于商品数量与套餐配置的SQL查询需求实现问询

需求明确 #

• 第一轮筛选：直接保留brand=1且manufacturer=2的商品
• 第二轮筛选：从第一轮未选中的商品里，挑出同一delivery维度下，无法满足PackageQuantity=3的套餐配置规则的商品。核心要求是套餐规则由ConfigGroup定义，比如ConfigGroup4要求必须同时包含qty1和qty2类型的商品，而非仅总量达标。

核心修正点 #

原SQL的问题在于只校验了商品总量，忽略了ConfigGroup要求的商品类型组合必须存在这一前提。解决思路：

1. 拆分出第一轮筛选的商品和剩余待校验的商品
2. 按delivery分组，针对对应ConfigGroup的规则，先校验必要的商品类型是否齐全，再校验总量是否满足PackageQuantity=3
3. 合并两轮筛选的结果

示例表结构（参考） #

假设涉及两张核心表：

• products：product_id, brand, manufacturer, delivery, qty_type, qty, config_group
• config_rules：config_group, required_qty_types, target_total_qty（比如ConfigGroup4的required_qty_types为'qty1,qty2'，target_total_qty=3）

修正后的SQL代码 #

WITH first_pass AS (
    -- 第一轮筛选结果
    SELECT *
    FROM products
    WHERE brand = 1 AND manufacturer = 2
),
remaining AS (
    -- 第一轮筛选后剩下的商品
    SELECT *
    FROM products
    WHERE NOT (brand = 1 AND manufacturer = 2)
),
delivery_validation AS (
    -- 按delivery分组，校验是否满足对应ConfigGroup的套餐规则
    SELECT
        r.delivery,
        r.config_group,
        -- 针对ConfigGroup4的硬编码示例（多规则场景可改用动态逻辑）
        CASE
            WHEN r.config_group = 'ConfigGroup4' THEN
                CASE
                    WHEN EXISTS (SELECT 1 FROM remaining r2 WHERE r2.delivery = r.delivery AND r2.qty_type = 'qty1')
                         AND EXISTS (SELECT 1 FROM remaining r2 WHERE r2.delivery = r.delivery AND r2.qty_type = 'qty2')
                         AND SUM(r.qty) >= 3
                    THEN 1 -- 满足套餐
                    ELSE 0 -- 无法满足
                END
            -- 可添加其他ConfigGroup的校验逻辑
            ELSE 0
        END AS meets_package
    FROM remaining r
    GROUP BY r.delivery, r.config_group
)
-- 最终结果：第一轮商品 + 剩余中无法满足套餐的商品
SELECT * FROM first_pass
UNION ALL
SELECT r.*
FROM remaining r
JOIN delivery_validation dv 
    ON r.delivery = dv.delivery 
    AND r.config_group = dv.config_group
WHERE dv.meets_package = 0;

多规则扩展说明 #

如果有多个ConfigGroup规则，不想硬编码，可以用字符串拆分函数处理config_rules表中的required_qty_types，动态校验每个必要的qty_type是否存在（以PostgreSQL为例）：

WITH config_with_types AS (
    SELECT 
        config_group,
        string_to_array(required_qty_types, ',') AS required_types,
        target_total_qty
    FROM config_rules
),
delivery_validation AS (
    SELECT
        r.delivery,
        r.config_group,
        CASE
            WHEN (
                -- 校验所有必要类型都存在
                SELECT COUNT(DISTINCT r2.qty_type)
                FROM remaining r2
                JOIN config_with_types cwt ON r2.config_group = cwt.config_group
                WHERE r2.delivery = r.delivery
                AND r2.qty_type = ANY(cwt.required_types)
            ) = array_length(cwt.required_types, 1)
            AND SUM(r.qty) >= cwt.target_total_qty
        THEN 1
        ELSE 0
        END AS meets_package
    FROM remaining r
    JOIN config_with_types cwt ON r.config_group = cwt.config_group
    GROUP BY r.delivery, r.config_group, cwt.required_types, cwt.target_total_qty
)
-- 后续合并逻辑同前
SELECT * FROM first_pass
UNION ALL
SELECT r.*
FROM remaining r
JOIN delivery_validation dv 
    ON r.delivery = dv.delivery 
    AND r.config_group = dv.config_group
WHERE dv.meets_package = 0;

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