SQL如果同一组中的另一个字段包含特定值，则将该字段相乘

Question

我有一个很小的数据集

SELECT PID, PCODE, PVAL
FROM mytable

输出：

PID PCODE PVAL
2   N50   £1,000.00
2   B10   £100.00
2   P40   £500.00
3   B10   £100.00
3   P40   £500.00
4   B10   £100.00
4   P40   £500.00

我想输出相同的字段，除非PID组中有N50的PCODE，那么它们的B10需要减少N50的5%，如下所示。

预期输出：

PID PCODE PVAL
2   N50   £1,000.00
2   B10   £50.00
2   P40   £500.00
3   B10   £100.00
3   P40   £500.00
4   B10   £100.00
4   P40   £500.00

我尝试使用子查询this和case语句，但无法获得匹配的值。

Answer 1

您可以使用case表达式和max函数来执行此操作。这假设一个pid只能有一行包含pcode=N50。

select pid,pcode
,case when pcode='B10' then pval-0.05*max(case when pcode='N50' then pval else 0 end) over(partition by pid)
 else pval end as pval
from mytable t

编辑:将结果限制为包含B10代码的年份的一种方法是

select pid,pcode
,case when pcode='B10' then pval-0.05*max(case when pcode='N50' then pval else 0 end) over(partition by pid)
 else pval end as pval
from mytable t
where yearcolumn in (select yearcolumn from mytable where pcode='B10')

Answer 2

您可以使用分析函数来执行此操作：

SELECT PID, PCODE,
       (PVAL -
        COALESCE(SUM(CASE WHEN PCODE = 'N50' THEN PVAL * 0.05 END) OVER (PARTITION BY PID), 0)
       ) as PVAL
FROM mytable;

实际上，COALESCE()并不是必需的：

SELECT PID, PCODE,
       (PVAL -
        SUM(CASE WHEN PCODE = 'N50' THEN PVAL * 0.05 ELSE 0 END) OVER (PARTITION BY PID)
       ) as PVAL
FROM mytable;

Answer 3

此解决方案仅在主表具有“B10”而第二个表具有“N50”时使用左连接来获取匹配。如果存在匹配项:返回N50 PVAL的5%折扣。如果不匹配:返回PVAL

SELECT t1.PID, T1.PCODE, t1.PVAL - isnull( .05 * t2.PVAL, 0) PVAL
  FROM mytable t1
    left join #mytable t2 
        on t1.PID = t2.PID and t1.PCode = 'B10' and t2.PCode = 'N50'