如何在SQL中按条件实现基于客户ID的行列数值相乘？

核心思路 #

用条件聚合把每个客户ID对应的CAP=1、CAP=2的CAP值和MKT值分别提取出来，要是某个CAP类型不存在（比如只有CAP=1没有CAP=2），就用1代替对应的空值——因为乘以1不会改变最终结果，这样就能用统一的逻辑覆盖两种场景，不用分开写判断。

完整SQL代码 #

SELECT
    t1.customer_id,
    t1.Qnt *
    COALESCE(MAX(CASE WHEN t2.CAP = 1 THEN t2.CAP END), 1) *
    COALESCE(MAX(CASE WHEN t2.CAP = 1 THEN t2.MKT END), 1) *
    COALESCE(MAX(CASE WHEN t2.CAP = 2 THEN t2.CAP END), 1) *
    COALESCE(MAX(CASE WHEN t2.CAP = 2 THEN t2.MKT END), 1) AS calculated_value
FROM
    table1 t1
JOIN
    table2 t2 ON t1.customer_id = t2.customer_id
WHERE
    t2.CAP IN (1, 2)
GROUP BY
    t1.customer_id, t1.Qnt;

代码细节解释 #

1. 条件聚合取值：比如MAX(CASE WHEN t2.CAP = 1 THEN t2.CAP END)，会找出当前客户CAP=1对应的CAP值，要是这个客户没有CAP=1的记录，结果就是NULL；同理提取CAP=1的MKT值、CAP=2的CAP和MKT值。
2. COALESCE处理空值：COALESCE(..., 1)把空值替换成1，这样当客户只有CAP=1时，CAP2和MKT2对应的部分就是乘1，相当于只计算Qnt * CAP1 * MKT1；如果同时有CAP1和CAP2，就正常相乘所有数值。
3. 关联与过滤：通过JOIN关联两张表，WHERE子句只保留CAP为1或2的记录，避免无关数据干扰。
4. 分组逻辑：按客户ID和Qnt分组，保证每个客户只出一条计算结果。

可选简化方案（支持PIVOT的数据库） #

如果你的数据库（比如Oracle、SQL Server）支持PIVOT语法，可以先把table2转成宽表，再计算，逻辑更直观：

WITH pivoted_table AS (
    SELECT
        customer_id,
        MAX(CASE WHEN CAP = 1 THEN CAP END) AS cap1,
        MAX(CASE WHEN CAP = 1 THEN MKT END) AS mkt1,
        MAX(CASE WHEN CAP = 2 THEN CAP END) AS cap2,
        MAX(CASE WHEN CAP = 2 THEN MKT END) AS mkt2
    FROM table2
    WHERE CAP IN (1, 2)
    GROUP BY customer_id
)
SELECT
    t1.customer_id,
    t1.Qnt *
    COALESCE(p.cap1, 1) *
    COALESCE(p.mkt1, 1) *
    COALESCE(p.cap2, 1) *
    COALESCE(p.mkt2, 1) AS calculated_value
FROM table1 t1
JOIN pivoted_table p ON t1.customer_id = p.customer_id;

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