SQL - 在同一个表中乘值

Question

我有以下代码：

SELECT ConsumerID, Product, 
 
     (frequency * Product_Value) as Value_productA 
 
     (frequency * Product_Value) as Value_productB 
 
     (frequency * Product_Value) as value_productC 
 
From Table1

的table1的样子说：

ConsumerID | Product | Product_value | frequency | value_productA | value_productB | value_productC 
 
----------------------------------------------------------------- 
 
001 | productA | 5$ | 2 | | | | 
 
001 | productB | 7$ | 4 | | | | 
 
001 | productC | 9$ | 6 | | | | 
 
002 | productA | 5$ | 2 | | | | 
 
002 | productB | 7$ | 4 | | | | 
 
002 | productC | 9$ | 6 | | | | 
 

 

 
Expected Bevaviour: 
 

 
ConsumerID | Product | Product_value | frequency | value_productA | value_productB | value_productC 
 
----------------------------------------------------------------- 
 
001 | productA | 5$ | 2 |10$ | | | 
 
001 | productB | 7$ | 4 | |28$ | | 
 
001 | productC | 9$ | 6 | | |54$ | 
 
002 | productA | 5$ | 2 | 10$| | | 
 
002 | productB | 7$ | 4 | |28$ | | 
 
002 | productC | 9$ | 6 | | |54$ | 
 

 

 
Current behaviour: 
 

 
ConsumerID | Product | Product_value | frequency | value_productA | value_productB | value_productC 
 
----------------------------------------------------------------- 
 
001 | productA | 5$ | 2 |10$ |10$ |10$ | 
 
001 | productB | 7$ | 4 |28$ |28$ |28$ | 
 
001 | productC | 9$ | 6 |54$ |54$ |54$ | 
 
002 | productA | 5$ | 2 |10$ |10$ |10$ | 
 
002 | productB | 7$ | 4 |28$ |28$ |28$ | 
 
002 | productC | 9$ | 6 |54$ |54$ |54$ |

的想法是计算t他为每个客户端产品&产品 - 含义频率* product_value。示例代码正确计算SUM，但将其写入每个字符而不是仅写入value_productX字段。

Answer 1

你需要一些条件逻辑，如case：

SELECT ConsumerID, Product, 
     (case when product = 'productA' then (frequency * Product_Value) end) as Value_productA 
     (case when product = 'productB' then (frequency * Product_Value) end) as Value_productB 
     (case when product = 'productC' then (frequency * Product_Value) end) as value_productC 
From Table1; 

通常，当类似的问题是问，我们的目标是每consumerid得到一行。如果是这样的话，你想要聚合：

select ConsumerID, 
     sum(case when product = 'productA' then (frequency * Product_Value) end) as Value_productA 
     sum(case when product = 'productB' then (frequency * Product_Value) end) as Value_productB 
     sum(case when product = 'productC' then (frequency * Product_Value) end) as value_productC 
From Table1 
group by ConsumerID