如何编写SQL对含指定Code的相似行计算Value乘积

核心需求拆解 #

• 仅处理同一Name、Date start、Date end的行组
• 仅保留同时包含Code A、B、C的行组（忽略其他Code的存在）
• 计算这三个Code对应的Value的乘积

解决方案1：先筛选有效组再计算乘积 #

通过CTE先筛选出符合条件的(Name+日期)组，再关联原表计算乘积：

WITH valid_groups AS (
    SELECT Name, `Date start`, `Date end`
    FROM your_table
    WHERE Code IN ('A', 'B', 'C')
    GROUP BY Name, `Date start`, `Date end`
    HAVING 
        SUM(CASE WHEN Code = 'A' THEN 1 ELSE 0 END) > 0
        AND SUM(CASE WHEN Code = 'B' THEN 1 ELSE 0 END) > 0
        AND SUM(CASE WHEN Code = 'C' THEN 1 ELSE 0 END) > 0
)
SELECT 
    v.Name,
    v.`Date start`,
    v.`Date end`,
    EXP(SUM(LOG(t.Value))) AS abc_product
FROM valid_groups v
JOIN your_table t 
    ON v.Name = t.Name 
    AND v.`Date start` = t.`Date start` 
    AND v.`Date end` = t.`Date end`
WHERE t.Code IN ('A', 'B', 'C')
GROUP BY v.Name, v.`Date start`, v.`Date end`;

• valid_groups 子查询：通过CASE语句分别统计每组中A、B、C的存在情况，确保三个Code都至少有一条记录，避免仅靠数量判断的误差（比如组内有A、B、D三个Code的情况）
• 主查询：关联有效组与原表，仅取A、B、C的记录，用EXP(SUM(LOG(Value)))计算乘积（需保证Value为正数，否则LOG会报错）

解决方案2：用窗口函数标记有效组 #

通过窗口函数直接标记每组是否符合条件，再筛选计算：

WITH marked_data AS (
    SELECT 
        *,
        MAX(CASE WHEN Code = 'A' THEN 1 ELSE 0 END) OVER (PARTITION BY Name, `Date start`, `Date end`) AS has_a,
        MAX(CASE WHEN Code = 'B' THEN 1 ELSE 0 END) OVER (PARTITION BY Name, `Date start`, `Date end`) AS has_b,
        MAX(CASE WHEN Code = 'C' THEN 1 ELSE 0 END) OVER (PARTITION BY Name, `Date start`, `Date end`) AS has_c
    FROM your_table
    WHERE Code IN ('A', 'B', 'C')
)
SELECT 
    Name,
    `Date start`,
    `Date end`,
    EXP(SUM(LOG(Value))) AS abc_product
FROM marked_data
WHERE has_a = 1 AND has_b = 1 AND has_c = 1
GROUP BY Name, `Date start`, `Date end`;

• marked_data 子查询：用窗口函数MAX() OVER (PARTITION BY ...)标记每组是否包含A、B、C
• 主查询：筛选出三个标记都为1的组，再计算乘积

注意事项 #

• 如果Value存在0或负数，LOG函数会抛出错误，需提前过滤这类数据，或根据数据库特性使用自定义聚合函数实现乘积计算
• 字段名包含空格时，需用反引号（MySQL）或方括号（SQL Server）包裹，不同数据库语法略有差异

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