主页 / dba / 问题 / 255832
Accepted
TheDemonLord
Asked: 2019-12-18 14:19:05 +0800 CST

将多个列中的几个离散值相乘，然后求和

  • 772

我需要将两个表中的值相乘并对结果求和。该数据库是 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 个表之间加入，然后将值相乘。使用子查询和后续连接？

sql-server postgresql

1 个回答

  1. Best Answer

    此查询为表中的每个客户生成您想要的结果：

    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。

    关系设计似乎有点神秘。我希望你有充分的理由。

    • 2

相关问题