將多個列中的幾個離散值相乘，然後求和

我需要將兩個表中的值相乘並對結果求和。該數據庫是 Postgres 或 MSSQL（長篇大論）。

桌子Bought_Products

ID | Name | Customer_ID | Product_ID | Foo | Bar | Blarg
1  | test | 123         | 321        | NULL| 1   | NULL
2  | tast | 123         | 231        | 5   | NULL| 20
3  | tost | 456         | 321        | NULL| 3   | NULL

桌子Price

ID | Item | Product_ID | Cost
1  | Foo  | 321        | 10
2  | Bar  | 321        | 5
3  | BASE | 321        | 100
4  | BASE | 231        | 50
5  | Blarg| 231        | 0.5
6  | Foo  | 231        | 1

我需要這樣的結果：

Customer | Products | Revenue
123      | 321, 231 | 170

對於客戶 123，有 2 個產品：321 和 231，底價分別為 100 和 50；在 5 時加上 1 個“酒吧”項目；在 1 處加上 5 x ‘Foo’ 物品；和 20 個 0.5 的“Blarg”項目。

這就是我的開始：

select sum(p.cost) from price p
join Bought_Products bp on bp.product_id = p.product_ID
where p.item = 'BASE'

我需要將相關的 ‘Foo’、‘Bar’ 或 ‘Blarg’ 值Bought_Products與各自相乘Price.Cost（將列標題與項目匹配）。

我還沒有在 SQL 中使用過基本的數學函式。我不確定如何在 2 個表之間加入，然後將值相乘。使用子查詢和後續連接？

此查詢為表中的每個客戶生成您想要的結果：

SELECT customer_id AS customer
    , string_agg(DISTINCT product_id::text, ', ') AS products
    , sum(cost * CASE p.item
                    WHEN 'BASE'  THEN 1
                    WHEN 'Foo'   THEN b.foo
                    WHEN 'Bar'   THEN b.bar
                    WHEN 'Blarg' THEN b.blarg
                 END) AS revenue
FROM   bought_products b
LEFT   JOIN price p USING (product_id)
-- WHERE  p.item = ANY ('{BASE,Foo,Bar,Blarg}')
GROUP  BY 1;

db<>在這裡擺弄

我們只需要加入一次。魔術發生在CASE表達式中，取決於Item.

LEFT [OUTER] JOIN即使沒有任何匹配的價格行，我也會將每個產品都保留在結果中。用於[INNER] JOIN刪除那些。和/或啟動註釋WHERE子句以達到相同的效果。

ID 1順便說一句，表格中的行很Price突出，因為相關的行Bought_Products有Foo IS NULL。

關係設計似乎有點神秘。我希望你有充分的理由。

引用自：https://dba.stackexchange.com/questions/255832